cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
teckchan
Helper III
Helper III

Showing % totals of categories in Matrix

Hey Everyone,
I am trying to re-create a tableau report on Power BI. In tableau I have a matrix like this (rows: product name, product version; column: os version; values: # of machines, % of machines)

teckchan_0-1622575077778.png

there is just one primary field for values - # of machines. The % of machines here is the % for each "product name". in tableau we have the option to show value as percentage and compute using "pane" where as in Power BI I can only show percentages as row total, column total or grand total. How do I replicate this % calculation in Power BI?

teckchan_1-1622575751890.png

I tried to use a measure to calculate this:

MeasureC =
var Product_name = VALUES('Product Versions'[Product Name])
var Product_version = VALUES('Product Versions'[product_version])
var Machines = SUM('Product Versions'[# of Machines])
var Total_machines =
SWITCH(
TRUE(),
ISINSCOPE('Product Versions'[os_version]),CALCULATE(SUM('Product Versions'[# of Machines]),ALLSELECTED('Product Versions'),'Product Versions'[Product Name] in Product_name,'Product Versions'[product_version] in Product_version),
ISINSCOPE('Product Versions'[product_version]),CALCULATE(SUM('Product Versions'[# of Machines]),ALLSELECTED('Product Versions'),'Product Versions'[Product Name] in Product_name),
ISINSCOPE('Product Versions'[Product Name]),CALCULATE(SUM('Product Versions'[# of Machines]),ALLSELECTED('Product Versions'))
)
return
IF(
ISINSCOPE('Product Versions'[product_version]),
DIVIDE(Machines,Total_machines),
1
)

Here is what my pbi matrix looks like currently (incorrect):

teckchan_2-1622576032167.png

Thanks!

1 ACCEPTED SOLUTION

I think this should do it.

 

MeasureC = 
DIVIDE (
    SUM ( 'Product Versions'[# of Machines] ),
    CALCULATE (
        SUM ( 'Product Versions'[# of Machines] ),
        ALLSELECTED ( 'OS Version Order' ),
        ALLSELECTED ( 'Product Versions'[product_version] )
    )
)

View solution in original post

8 REPLIES 8
AlexisOlson
Community Champion
Community Champion

It looks like you want your denominator to be the total for that Product Name. In this case, you don't really need ISINSCOPE.

 

MeasureC =
DIVIDE (
    SUM ( 'Product Versions'[# of Machines] ),
    CALCULATE (
        SUM ( 'Product Versions'[# of Machines] ),
        ALLSELECTED ( 'Product Versions' ),
        ALLEXCEPT ( 'Product Versions', 'Product Versions'[Product Name] )
    )
)

Hey @AlexisOlson ,
Thanks for your reply. Yes, the denominator should be the total for that product name. I tried your measure but I still don't get the correct numbers (PFA image below). I am looking to make the rows showing totals at the product name level "100%" (highlighted cells below) and then the break up by product version. 

teckchan_0-1622590978050.png

 

There's some filter or context that I'm not seeing. Any chance you can share the file or some sample version of it?

Hey @AlexisOlson ,
Sure thing. Here's the link to the sample file on my drive. Please do let me know if you're having any trouble downloading it. Thanks.
Best,
Akash

I think this should do it.

 

MeasureC = 
DIVIDE (
    SUM ( 'Product Versions'[# of Machines] ),
    CALCULATE (
        SUM ( 'Product Versions'[# of Machines] ),
        ALLSELECTED ( 'OS Version Order' ),
        ALLSELECTED ( 'Product Versions'[product_version] )
    )
)

View solution in original post

This worked well! thank you so much @AlexisOlson 

Hmm. Are there any tables involved other than 'Product Versions'?

Hey @AlexisOlson ,
Nope. this is the only primary table. I just use another table just to get the OS versions in my desired order

teckchan_0-1622648991604.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors