One Measure With Different Calculations Depending on Matrix Row Group Value

[meehael]

Hi everyone,

I need to add three extra categories or groups at the bottom of a matrix and do a different calculation for them.

For example, let's say I have a matrix with one category/group "Car Make" and one value which displays a count.

The matrix would look something like this:

Audi	10
BMW	15
Chrysler	20
---TOTAL---	45 (Sum of the values above)
---AVERAGE---	15 (Average of the values above)
---ETC---	Some other calculation

"Car Make" populates the matrix dynamically, depending on availability, but the bottom three rows/groups are fixed and need to be displayed always.

For this reason, I have created a new table which generates only one column with distinct values for car make, plus I manually added the three rows to that table.

I was hoping to create a measure with an IF or SWITCH statement that checks matrix group name and then calculates appropriately, but this obviously doesn't work:

Measure = CALCULATE(IF(SELECTEDVALUE(CarMakeUnion[name])="---TOTAL---"; SUM(CarMakeUnion[count]); COUNT(CarMakeUnion[name])))

Is it possible to do something like this?

Or should I take a different approach?

I need to have it in only one matrix, however.

Any help is appreciated.

Thanks and best regards,

Mike

[sturlaws · Accepted solution]

Hi @meehael 

I am a bit confused by your naming of your table, so I created this sample report with some moclup data: pbix 

I have written the measure like

measure = 
VAR _sv =
    CALCULATE ( SELECTEDVALUE ( MatrixRows[MatrixRows] ) )
RETURN
    SWITCH (
        TRUE ();
        _sv = "Total"; CALCULATE ( COUNTROWS ( cars ); ALL ( Cars ) );
        _sv = "Average"; CALCULATE(AVERAGEX ( VALUES ( Cars[CarName] ); CALCULATE(COUNTROWS ( Cars ) ));ALL(Cars));
        _sv = "Other"; BLANK ();
        COUNTROWS ( Cars )
    )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[meehael]

Thank you @sturlaws !

You gave me an idea which made it work in the end.

Best regards,

Mike

[Tad17]

Hey @meehael 

You cannot add multiple total rows to a matrix in Power BI. I would suggest sticking the measures on cards beneath the table/matrix.