CODEWITHSUNDEEP
powerbidax
K R
K R

Reputation: 21

Power BI - Average Per Category

I am brand new to Power BI, and I could use a bit of help. I have this sample data:

enter image description here

I then want to get the [Average Total Billed Per Profession], which I got using:

enter image description here

Then, ideally, I would [Total Billed]/[Average Total Billed Per Profession]. But when I drop it in a table, it breaks. On the left, are the values I would want in the calculation. On the right, the values I get:

enter image description here

How would I set up the calculation correctly? Any help is appreciated. Thanks.

Upvotes: 0

Views: 12477

Answers (2)

yk1031
yk1031

Reputation: 21

It looks like profession + name doesn't make each row unique. If you just want "Average Total Billed Per Profession", then you can use something like the following.

Average Total Bille Per Profession =

AVERAGEX(
    VALUES( StaticData[Profession] ), 
    SUM( StaticData[Price] )
)

It's just making sure you're considering unique profession for the metric you're calculating.

Upvotes: 1

sergiom
sergiom

Reputation: 4887

To have the average per profession in a table where also the Name is present, it's necessary to remove the filter context over Name, so a possible solution is

Average Total Bille Per Profession =
CALCULATE(
    AVERAGEX(
        SUMMARIZE( StaticData, StaticData[Profession], StaticData[Name] ),
        CALCULATE( SUM( StaticData[Price] ) )
    ),
    ALLEXCEPT( StaticData, StaticData[Profession] )
)

The CALCULATE inside AVERAGEX is required to trigger a context transition to transform the row context on the StaticData columns to a the corresponding filter context, that's needed to filer the StaticData[PriceColumn] rows to be used in the SUM.

Upvotes: 2

Related Questions