Forum Discussion
Power BI - Dynamic View Options - Matrix
Hi hallmarke14 ,
I create a table as you mentioned.
Then I think you can create some measures and here are the DAX codes.
UnitsTYShow = SUM(SalesData[UnitsTY])
UnitsSDLYShow = SUM(SalesData[UnitsSDLY])
%CHGUnitsShow = DIVIDE([UnitsTYShow] - [UnitsSDLYShow], [UnitsSDLYShow], 0)
SalesTYShow = SUM(SalesData[SalesTY])
SalesSDLYShow = SUM(SalesData[SalesSDLY])
%CHGSalesShow = DIVIDE([SalesTYShow] - [SalesSDLYShow], [SalesSDLYShow], 0)
You can also create colors on them.
%CHGUnitsColor = IF([%CHGUnitsShow] < 0, "Red", "Green")
%CHGSalesColor = IF([%CHGSalesShow] < 0, "Red", "Green")
Then I create a new table and also create a measure.
ColumnSelector =
DATATABLE(
"Column", STRING,
{
{"Units TY"},
{"Units SDLY"},
{"% CHG Units"},
{"Sales TY"},
{"Sales SDLY"},
{"% CHG Sales"}
}
)
SelectedMeasureShow =
SWITCH(
SELECTEDVALUE(ColumnSelector[Column]),
"Units TY", FORMAT([UnitsTYShow], "#,##0"),
"Units SDLY", FORMAT([UnitsSDLYShow], "#,##0"),
"% CHG Units", FORMAT([%CHGUnitsShow], "0.0%"),
"Sales TY", FORMAT([SalesTYShow], "$#,##0"),
"Sales SDLY", FORMAT([SalesSDLYShow], "$#,##0"),
"% CHG Sales", FORMAT([%CHGUnitsShow], "0.0%")
)
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, Yilong!
I tried adding the color measures, but they don't actually change the color of the values. All it did was show the string values "red" or "green" based on the IF condition. I was hopeful when I saw your idea. I appreciate your effort!
Hi hallmarke14 ,
If you are only coloring other columns, in Powerbi you can just select Conditional formatting to change it.
But if you want to colorize based on your choices in Slicer, my suggestion would be to aggregate Measure in the same format for modification.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I have only one measure in Values and that populates the matrix. We have several Channels, which is what our columns are that we want for comparison. If I choose conditional formatting for that measure, the changes apply to all values, not just the % Chg columns.
If I apply conditional formatting, these values will be colorized, but when I change the selection to Units, those are colorized, as well, and we do not want that.