Forum Discussion

Clara's avatar
Clara
Advocate II
7 years ago
Solved

Aggregate differently by category

Let's say I have dozens of store units for which I have Revenue, Profit and Profitability values for each month since January 2016 (date format is DD/MM/YYYY):   Unit Type Date Value AA R...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Clara,

     

     

    Yes there is.

    You will need a measure though, like this one:

     

    Value measure = IF (
        FIRSTNONBLANK ( Table1[Type], 1 ) = "Profitability",
        AVERAGE ( 'Table1'[Value] ) ,
        CALCULATE (
            SUMX (Table1,('Table1'[Value])
            )
        )
    )

    Instead of adding the value column as the value in matrix, use the Value measure instead.

     

    It gives this result:

     

    Note that the calculation is not correct. I just used your sample to have some test data.

     

    Best regards

    Kaj

Clara's avatar
Clara
Advocate II
7 years ago

Anonymous Thank you so much! I've made one slight modification to your measure so as to ignore null values in the average, and got exactly what I wanted:

 

 

Value measure = IF (
        FIRSTNONBLANK ( Table1[Type], 1 ) = "Profitability",
        CALCULATE ( AVERAGE ('Table1'[Value]), FILTER('Table1', 'Table1'[Value]<>0) ),
        CALCULATE (
            SUMX (Table1,('Table1'[Value])
            )
        )
    )

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago
You're most welcome! Glad I could help :)