Showing % totals of categories in Matrix

[Anonymous]

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)

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?

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

Thanks!

[AlexisOlson]

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

[Anonymous]

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. 

[AlexisOlson]

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

[Anonymous]

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

[AlexisOlson · 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] )
    )
)

[Anonymous]

This worked well! thank you so much @AlexisOlson 

[AlexisOlson]

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

[Anonymous]

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