percentage subtotals

[chriscorpion786]

Hi All,
Im trying to find a DAX solution to evaluate percentages for subtotals. I have 2 columns, SubFamily and within it I have Brand names, so I need to find the percentage of each
Brand within that Sub family and the sub family should also show percentage of the grand total.
It will be clear in the attached image I have uploaded. Iron Steam is a sub family name so the 12.2% share shows its weight in the category of sub family name, at same time,within this category there are many brands for e.g Philips shows 34.95% share withing the Brands. All these are correct and I got a solution from the internet, but I didnt understand it.
Is there a better way of writing it in DAX which makes more sense to do this.
Please help.

 

[JustynaMK]

Hi Chris,

Can you share what is the current DAX measure that you are using to calculate the percentages so that I can explain it to you and see if there's anything that can be improved?
Unfortunately, I couldn't find it on your screenshot.

 

[chriscorpion786]

Hi Justyna,

Thanks for replying, I got this solution from the web,but I couldn't make sense of it, although it works.
Brand % Shares = if([Brand Sales %]=1,[Sub Family %],[Brand Sales %])
Brand Sales % = DIVIDE([Total Value],[Total Brand Sales],0)
Sub Family % = DIVIDE([Total Value],[Total Sub Family Name],0)
Its like in a pivot table you have 2 categories, Subfamily and Brand and then you want to find the % of parent row total, which will give you the Brand share within the category and also show the Category weightage from grand total. Hope that its clear, Im just trying to find a better DAX formula for this.
Is there a way I can upload the file, I would be able to share if you need.
Many thanks,

 

[JustynaMK]

Hi Chris,

Sorry for the delay! UK long weekend made me quite lazy

Thanks for providing the explanation. These three formulas are actually quite smart - let's start with [Brand Sales %]. If you add it as an additional column to your matrix visualization it will become clear what it is doing. This measure shows % of grand total for each brand within a given "Sub Family" - apart from the "Sub Family" row itself, where it will show 100% (=1).

For these "subtotal" rows, where [Brand Sales %] equals 1 (100%), we actually want to return its share in the total of all "Sub Families" - thus we need to use [Sub Family %] measure.

Combined, we end up with [Brand % Shares] measure: if([Brand Sales %]=1,[Sub Family %],[Brand Sales %])

Alternatively, you can always try using the following measure, but I doubt it will be any faster than what you have already implemented -

Brand % Shares = 
    VAR TotalValue = SUM(tbl_SubFamily[Total Value])
    VAR HierarchyLvl = COUNTROWS(tbl_SubFamily)
    VAR HierarchyTotal = 
        IF(HierarchyLvl = 1,
        CALCULATE(
            SUM(tbl_SubFamily[Total Value]),
            ALLEXCEPT(tbl_SubFamily, tbl_SubFamily[Sub Family Name], tbl_SubFamily[Year])
        ),
        CALCULATE(
            SUM(tbl_SubFamily[Total Value]),
            ALLSELECTED()
        ))
return
    DIVIDE(TotalValue, HierarchyTotal)

 

[chriscorpion786]

Hi Justina, thanks for your reply.
My table name is EPOS and within that I have categories SubFamilyName and Brand name, so,I dont know if your formula will work as I dont see calculation for Brand.
Could you rephrase your DAX calculation using EPOS as table name and Subfamilyname & Brand as column names within the EPOS table.
Your DAX formula should show the category weight and Brand share within each category. Is there a way I could upload the files here...to see if it helps.
Please also kindly explain your DAX when you do the formula. Thank you so much...

 

[JustynaMK]

Hi Chris,

Sure - please see the adjusted version below.

There's no need to calculate Brand as it is a separate column in my dataset - unless your data source looks differently? Feel free to share your own screenshot.

Here's an adjusted measure with additional comments which will hopefully clarify what each line is doing -

Brand % Shares = 
    //The first variable simply calculates Total Value.
    VAR TotalValue = SUM(EPOS[Total Value])
    
    //Calculate Hierarchy Level by counting rows. Once we use this measure in our matrix, it will result in:
    //   - Number of rows: 1, for rows showing Brand
    //   - Number of rows: greater than 1, for rows showing SubFamilyName
    VAR HierarchyLvl = COUNTROWS(EPOS)
    //Our last variable. It calculates a SUM of Total Value for:
    //   - A chosen Brand, if HierarchyLvl equals 1. In order to calculate a proper SUM, we need to use ALLEXCEPT function to group our dataset by SubFamilyName and Year.
    //   - A chosen SubFamilyName, if HierarchyLvl is greater than 1. In order to calculate a proper SUM, we need to take ALL SELECTED values into consideration.
    VAR HierarchyTotal = 
        IF(HierarchyLvl = 1,
        CALCULATE(
            SUM(EPOS[Total Value]),
            ALLEXCEPT(EPOS, EPOS[SubFamilyName], EPOS[Year])
        ),
        CALCULATE(
            SUM(EPOS[Total Value]),
            ALLSELECTED()
        ))
    //The final result divides a current TotalValue (see first variable) by HierarchyTotal (see last variable).
    Var Result = DIVIDE(TotalValue, HierarchyTotal)
    
return
    Result

One more tip -
If you are still unsure what each part of the measure is doing, you can simply replace the part after "return" with a variable name. For example, if you want to see what HierarchyLvl (the second variable) is doing, simply change the end of the measure from "return Result" to "return HierarchyLvl" and see how the matrix changes -

I hope it helps.

 

[chriscorpion786]

Hi Justina,
I cracked my brains trying to figure it out, but couldn't unfortunately. The above formula wasn't giving me the internal correct Brand shares but subfamily shares were correct. However,I figured out a way to get it. Please see the below formula using isinscope. I'm attaching the images as well. The Brand share for Braun should be 32.4% in Year 2019 under Hand Blender subfamily.

Brand % Shares =
var subfamilypercent= DIVIDE([Total Sales],CALCULATE([Total Sales],ALLSELECTED(EPOS[Sub Family Name],EPOS[Year])),0)

var brandpercent = DIVIDE([Total Sales],CALCULATE([Total Sales],ALLSELECTED(EPOS[Brand],EPOS[Year])),0)

return
if(ISINSCOPE(EPOS[Brand]),brandpercent,subfamilypercent)


Many thanks for your replies and hope we all benefit in this learning. Feel free to share your views or a revised version of your formula, its always good to know other workarounds as well. Thanks again.

 

[JustynaMK]

Hi Chris,

Thank you very much for sharing your discoveries! I am glad you solved your problem

Just out of my own curiosity, as I'm trying to understand where the issue lies, could you share a screenshot of your dataset? I wonder if the structure of underlying data caused the difference in function's behavior. Also, is [Total Value] a separate measure?

As you can see, your measure and mine bring exactly the same results. There must be some hidden difference that I'm very eager to discover

 

[chriscorpion786]

Hi Justina,
Sure... I can share....lets get down to this......
Lets take subfamily of Blender and Brand name = "Braun", this share in 2019 was 3.15% and the Category of Blender share to all category was 8.88%. This is correct.
As per your formula, your category share for Blender is correct of 8.88% , but the Braun share is showing as 0.28% which is not correct. Maybe you can adjust the formula to show the correct Brand shares. ( How does countrows(EPOS) for HierarchyLvl in your formula help...I didn't understand this).

Also, EPOS[Total Sales] is the column from the EPOS table, and I created an explicit measure of Total Sales=sum(EPOS[Total Sales]).
I'm sharing the excel pivot table screen shot that shows the right calculation, in the pivot table I used % of Parent row total to get the correct result.
Let me know your findings....by the way....I would need assistance in another DAX formula....I'm trying to crack it...but if I am not able to get it, will share with you..if its OK.

Thanks

 

[JustynaMK]

Thanks Chris!

I tried to tweak both formulas but I still cannot make them to show different results. I noticed that the only real difference between my version of calculation for Brand and your version is:
My formula (now with Total Sales measure) -> CALCULATE([TotalSales], ALLEXCEPT(EPOS, EPOS[SubFamilyName], EPOS[Year]))
Your formula -> CALCULATE([TotalSales],ALLSELECTED(EPOS[Brand],EPOS[Year])

I suspect that if you return these two calculations you'll receive two different results. On my side they bring the same totals:

If that is the case, then it means that there are some other filters that affect your table (you can check it by selecting the table and hovering over "Filters" option):

Alternatively, is your table linked to other tables in a Data Model, or is it a standalone table?

I know this is absolutely nothing urgent as you already found a solution, so no rush with the response I'm just being curious.

As for the CountRows, sorry for not explaining it earlier!
CountRows(EPOS) for HierarchyLvl are returning the following results -

It simply counts the number of rows from table EPOS that are included within each table row. For Brands (e.g. Bosch, Braun), we always have 1 row (see Excel table below). For SubFamilyName (e.g. Iron Steam), we always have more than one row (see Excel table below - data for Iron Steam consists of 5 rows).
In other words, it lets me determine if my formula is now looking at Brand or at SubFamilyName level (similar to what your INSCOPE formula is doing).