Percentage of a Parent Row Total

[newgirl]

Hello!

I havea  simple raw data of volume with business unit, brand, and date. I want to show a matrix showing the percentage of parent grand total. I read some articles and I started out with a small dataset in which I was able to create the matrix I want:

using this formula:

ParentVolume = 
var _Parent = IF(
    ISFILTERED('Table'[Brand]),
    CALCULATE(
        SUM('Table'[Volume]),
        ALL('Table'[Brand])),
        CALCULATE(
            SUM('Table'[Volume]),
            ALL('Table'[Business Unit])))
RETURN SUM('Table'[Volume])/_Parent

However, when I tried to apply it in my official report, it's only showing the percentage breakdown per brand but not the percentage breakdown per Business Unit

When viewing the per Business Unit level (it's showing 100% for every Business Unit)

When viewing the per Business Unit > per Brand level:

And here's my measure:

Volume_all_perBU % = 
VAR _parent =
IF(
    ISFILTERED('CR tbl_phrawsales'[Brand]),
    CALCULATE(
        SUM('CR tbl_phrawsales'[Actually billed amount]),
        ALL('CR tbl_phrawsales'[Brand])),
        CALCULATE(
            SUM('CR tbl_phrawsales'[Actually billed amount]),
            ALL('CR tbl_phrawsales'[Business Unit])))
RETURN divide(sum('CR tbl_phrawsales'[Actually billed amount]),_parent,0)

I already checked the row fields in the matrix, they are correct. These are the columns from CR tbl_phrawsales.

Can somebody help me figure out why it's not working on the per Business Unit level?

[v-yangliu-msft · Accepted solution]

Hi  @newgirl ,

I created some data:

Are you referring to calculating different levels in the matrix?

Here are the steps you can follow：

1. Create measure.

Measure =
IF(  
    ISINSCOPE('Table'[Group3]),4,
    IF(
ISINSCOPE('Table'[Group2])&&NOT(ISINSCOPE('Table'[Group3])),3,
IF(
    ISINSCOPE('Table'[Group1]),2,0)
))

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Abhis321]

@newgirl I want to achieve the same thing as posted by you in the very first img.
I have 3 dimension tables connected to one fact table... and I am using the rows from all 3 dim tables in a matrix in rows....I want to calculate percentage of parent row total just like you....please help.

Example here is ...5264 is 42% of total.....now I want that at Below(-)500 ..it will give me 3/5264...and similar for every row...for total of 100% ....
Further I have one more row .....which will follow the same thing as opened
@v-yangliu-msft ...please help