Forum Discussion

emmalim's avatar
emmalim
Frequent Visitor
3 years ago
Solved

Matrix computed row - to show computed columns with several measures

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 t...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    emmalim
    Frequent Visitor
    3 years ago

    Anonymous 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.  

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        emmalim
        Frequent Visitor
        3 years ago

        Anonymous  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!