replace measure item on matrix

[erihsehc]

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 those % calculation by measure (only for the % item)? not sure how to do that on matrix, please help thanks

Best regards

ER

[GilbertQ]

Hi @erihsehc

If I understand, what you are looking to do, is to use only the 2 highlighted measures in your matrix?

[erihsehc]

hi @GilbertQ, yes it is, I would like to use the measure for the 2 highlighted items in the matrix. A combination of item-measure-item-measure in the rows

[v-huizhn-msft]

Hi @erihsehc,

Please create some simple dummy data and list the expected result? It's really hard to reproduce your scenario without data.

Best Regards,
Angelia

[erihsehc]

hi @v-huizhn-msft,

I have attached the data model.

below is the expected result from Excel

below is the result from Power BI

Data model is as below

date model

Excel calculation

Best regards,

ER

[v-huizhn-msft]

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]

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 · Accepted solution]

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]

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

[v-huizhn-msft]

Hi @erihsehc,

This is your another issue, please mark the right reply as answer at first. Then create a new thread for new requirement. Thanks for understanding.

Thanks,
Angelia