Forum Discussion

erihsehc's avatar
erihsehc
Helper III
8 years ago
Solved

replace measure item on matrix

hi there,   Below are the screenshots on matrix and data source. the Consol PAT% on matrix is not from measure, so the % is incorrect, it sum up the % from data model. I do not know how to replace ...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi erihsehc,

    In excel, when the result is gotten based on rows. In Power BI, the calculation is based on column, so you need to type the original sample table below.

    sample table
    Then, create a new table using the formula below.

    Table = SUMMARIZE(Table1,Table1[item],"Sum of amt",SUM(Table1[amt]))



    Finally, get the right result by creating another new table based on the new table above.

    result = 
UNION (
    'Table' ,
    ROW (
        "item", "3.GP%",
        "Sum of amt", DIVIDE (
            CALCULATE (
                MAX ( 'Table'[sum of amt] ),
                FILTER ( 'Table', 'Table'[item] = "2.GP" )
            ),
            CALCULATE (
                MAX ( 'Table'[sum of amt] ),
                FILTER ( 'Table', 'Table'[item] = "1.sales" )
            )
        )
    ),
    ROW (
        "item", "6.PAT%",
        "Sum of amt", DIVIDE (
            (
                CALCULATE (
                    MAX ( 'Table'[Sum of amt] ),
                    FILTER ( 'Table', 'Table'[item] = "1.sales" )
                )
                    - CALCULATE (
                        MAX ( 'Table'[Sum of amt] ),
                        FILTER ( 'Table', 'Table'[item] = "5.Cost" )
                    )
            ),
            CALCULATE (
                MAX ( 'Table'[Sum of amt] ),
                FILTER ( 'Table', 'Table'[item] = "1.sales" )
            )
        )
    )
)

    expected result

    Please download the attachment to check more details.

    Best Regards,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

Hi erihsehc,

In excel, what calculation rules do you use? I can see it for it is an online excel. From your sample table, the excel result is uncorrect. Please see the rows highlighted in yellow background, 0.175+0.23=0.405~0.41, the rows highlighted in blue background 0.925+0.77=1.695~1.7, both of the result are correct in Power BI. In excel, how get 19% for GP%, 87% for PAT?

 



Best Regards,
Angelia

  • erihsehc's avatar
    erihsehc
    Helper III
    8 years ago

    hi v-huizhn-msft, product A GP%=35/200=17.5%, product B GP%=23/100=23%, overall GP%=(23+35)/(100+200)=19.3%, same calculation for PAT%

    the correct GP% should be 19.3% instead of 40.5%

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      8 years ago

      Hi erihsehc,

      In excel, when the result is gotten based on rows. In Power BI, the calculation is based on column, so you need to type the original sample table below.

      sample table
      Then, create a new table using the formula below.

      Table = SUMMARIZE(Table1,Table1[item],"Sum of amt",SUM(Table1[amt]))



      Finally, get the right result by creating another new table based on the new table above.

      result = 
UNION (
    'Table' ,
    ROW (
        "item", "3.GP%",
        "Sum of amt", DIVIDE (
            CALCULATE (
                MAX ( 'Table'[sum of amt] ),
                FILTER ( 'Table', 'Table'[item] = "2.GP" )
            ),
            CALCULATE (
                MAX ( 'Table'[sum of amt] ),
                FILTER ( 'Table', 'Table'[item] = "1.sales" )
            )
        )
    ),
    ROW (
        "item", "6.PAT%",
        "Sum of amt", DIVIDE (
            (
                CALCULATE (
                    MAX ( 'Table'[Sum of amt] ),
                    FILTER ( 'Table', 'Table'[item] = "1.sales" )
                )
                    - CALCULATE (
                        MAX ( 'Table'[Sum of amt] ),
                        FILTER ( 'Table', 'Table'[item] = "5.Cost" )
                    )
            ),
            CALCULATE (
                MAX ( 'Table'[Sum of amt] ),
                FILTER ( 'Table', 'Table'[item] = "1.sales" )
            )
        )
    )
)

      expected result

      Please download the attachment to check more details.

      Best Regards,
      Angelia

      • erihsehc's avatar
        erihsehc
        Helper III
        8 years ago

        thanks v-huizhn-msft, it is a very good method. However, it disconnected the connection with "product".

         

        Is that possible to enable the filter by product? to get the result by product and add product to the next level for drill down

         

        Best regards,

        ER