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

Need Help

7 Replies

  • 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