Aggregate values differently based on measure/logic?

[Chillytouch]

Hi,

I have a matrix that I created with example data shown below. I have been trying to get my data into a format like this for a while now however I am running into a road block with aggregation. The way the data source I made is, is I have a row per measure per fiscal period. so for instance I have a row that is fiscal period - 1 metric_type - revenue metric_value - 10000 and then records for salary and paypercentrev with the differetn metric_type values. So in the matrix in powerbi I put the metric_value in the "value" box and metric_type in the "columns" box above the year/period. However when I am drilling up/down by fiscal year/period it is summing all values naturally. However, for the "paypercentrev" values I don't want them summed, I want them averaged. Since it's the same column technically (metric_value), is this even possible? Is there any way to say, ok if column metric_type = "revenue" or "salary" then sum when you drill up/down but if metric_type = "paypercentrev" then average? Any advice would be appreciated. Thank you!

[v-rzhou-msft · Accepted solution]

Hi @Chillytouch ,

Do you want to show average if metric_type = "paypercentrev" , and show sum if metric_type = "revenue" or "salary"? I think you can try If function to achieve your goal.

My Sample:

Measure:

Measure = 
IF(MAX('Table'[metric_type])="paypercentrev",AVERAGE('Table'[Value]),SUM('Table'[Value]))

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Chillytouch , if you want to change the calculation based on level you can use isinscope

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/