Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
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.
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |