Matrix computed row - to show computed columns with several measures

[emmalim]

Hi @PaulDBrown , thanks for sharing the solution. I'm currently working on a dashboard for P&L statement, I found this sample file works very well for my scenario. However, my dashboard is required to show various columns with different calculation scenario.

Please allow me to use the sample power bi file to show the challenges I faced, I notice when I add additional calculation (for example add/minus 2 measures) to the column measure, the matrix will have error by having this message '  MdxScript(Model) (31,29) Calculation error in meassure '_measure' [Actual]: Cannot convert value 17.86% of type text to type numeric/date.

Appreciate if anyone can help to point out the root cause and how to resolve, thank you!

Original post: Solved: Calculate field in row header - Microsoft Fabric Community

[v-cgao-msft · Accepted solution]

Hi @emmalim ,

Currently the measure is of text type, you need to convert it to a numeric type.

Please check the pbix file.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[emmalim]

@v-cgao-msft Thanks! It is working well!

[v-cgao-msft · Accepted solution]

Hi @emmalim ,

Currently the measure is of text type, you need to convert it to a numeric type.

Please check the pbix file.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[emmalim]

@v-cgao-msft Thanks for your kind advice! This method works for my dashboard. Would like to check is it a must to create new measure series [_Actual 2] and [_Plan 2]?
Besides, noticed column  [_Actual 2] and [_Plan 2] did not show in % for the 3 rows, appreciate your support to share how to set the format.  

[v-cgao-msft]

Hi @emmalim ,

I'm guessing you're using [_Actual] to perform calculations with other values. In your model, it is partially returned by the Format() function, which returns a single string value. String cannot be directly computed with numeric type values. So we need a measure that returns a numeric type result, which is why I created [_Actual 2].

For the second question, please refer to this measure, which formats the results of the measure based on the values of the Structure column.

Actual - Plan = 
SWITCH(
    SELECTEDVALUE('P&L Rows'[Structure]),
    "% SVM", FORMAT([_Actual 2]-[_Plan 2],"Percent"),
    "% Op. Margin", FORMAT([_Actual 2]-[_Plan 2],"Percent"),
    "% EBT", FORMAT([_Actual 2]-[_Plan 2],"Percent"),
    FORMAT([_Actual 2]-[_Plan 2],"##0")
)

FORMAT – DAX Guide

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[emmalim]

@v-cgao-msft  Thanks for the support and explaination!
I encounter another issue whereby when I want to set the font colour based on value:
<0, show as red
>0, show as green
But the condition setting determine the column 'Actual - Plan' as Text:

I will need to set as below:

Appreciate your kind support to share the solution, thank you very much!

[emmalim]

@v-cgao-msft  Attached the BI file for your reference

BI file:

P&L Sample 

[v-cgao-msft · Accepted solution]

Hi @emmalim ,

Please new a measure:

_Actual2 - _Plan2 = [_Actual 2]-[_Plan 2] 

output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum