Forum Discussion

Chillytouch's avatar
Chillytouch
Frequent Visitor
4 years ago
Solved

Aggregate values differently based on measure/logic?

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!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

2 Replies