Percentage of Subtotal (Hierarchy)

[nathov]

Hi guys,

I'm trying to creat a DAX formula in Power BI to work similar to % of Parent Row Total in Excel, but it isn't working as I expected.

Dax formula:

Revenue % =

VAR CurrentRetailerType = VALUES(Sales[Retailer type])

VAR CurrentProductLine = VALUES(Sales[Product line])

VAR RevenueForCurrentSelection = SUM(Sales[Revenue])

VAR TotalRevenue =

SWITCH(

TRUE();

ISINSCOPE(Sales[Retailer country]);CALCULATE(SUM(Sales[Revenue]);ALLSELECTED(Sales);Sales[Retailer type] IN CurrentRetailerType;Sales[Product line] IN CurrentProductLine);

ISINSCOPE(Sales[Product line]);CALCULATE(SUM(Sales[Revenue]);ALLSELECTED(Sales);Sales[Retailer type] IN CurrentRetailerType);

ISINSCOPE(Sales[Retailer type]);CALCULATE(SUM(Sales[Revenue]);ALLSELECTED(Sales))

)

RETURN

IF(

ISINSCOPE(Sales[Retailer type]);

DIVIDE(RevenueForCurrentSelection;TotalRevenue);

1

)

I uploaded the excel file to show what I need and the PBIX file on this link:

https://we.tl/t-Ar641SRfN9 

Can anyone help me, please?

[amitchandak]

@nathov , refer if this can help

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[stevedep]

Hi,

Based on this article, it should be:

RatioToParent = 
IF (
    ISFILTERED (Sales[Retailer country]);
    SUM (Sales[Revenue])
    /
    
    CALCULATE (
        SUM (Sales[Revenue]);
        ALL (Sales[Retailer country])
  
    );
    IF (
        ISFILTERED (Sales[Product line]);
        SUM (Sales[Revenue])
        /
        CALCULATE (
            SUM (Sales[Revenue]);
            ALL (Sales[Product line])
        );
        IF (
            ISFILTERED (Sales[Retailer type]);
            SUM (Sales[Revenue])
            /
            CALCULATE (
                SUM (Sales[Revenue]);
                ALL (Sales[Retailer type])
            )
        )
    )
)

The numbers appear to be right. 

Link to Power BI file. 

Please mark as solution if this is what you are looking for. 

[nathov]

Hi @stevedep,

Thanks for trying but this formula doesn't work if I have some filters applied on the visual, that's the problem that I'm trying to figure out.

[stevedep]

@nathov , Please let us know if this works for you/

[stevedep]

Ah I see, to @Anonymous point, a star model is really required, no easy way around it. When you do this it will work:

Code:

RatioToParent = 
IF (
    ISFILTERED (country[Retailer country]);
  [countrycal];
    IF (
        ISFILTERED (line[Product line]);
      [productlinecalc];
        IF (
            ISFILTERED ('type'[Retailer type]);
          [typecalc]
    )
)

countrycal = CALCULATE( SUM (Sales[Revenue]))
    /
    
    CALCULATE (
       SUM (Sales[Revenue]);
        ALLSELECTED(country[Retailer country])
  
    )

The results are as expected:

Which you can see in the Power BI file, here. 

Please mark as solution if it works for you. 

Kudo's for the effort are appreciated.

Kind regards, 

Steve. 

[stevedep]

Please let us know if this worked for you.

[Anonymous]

There are a couple of things to note here.

First, this model is not following Best Practices. Please create a proper star schema with a hidden fact table and exposed dimensions. One of the dimensions should be Product (and most likely a date/time dimension since this is needed in each and every decent real-life model). Storing everything in one big table is not incorrect but it's making coding in DAX awkward and inefficient. Many a time also much harder.

Secondly, SWITCH must use ISINSCOPE from the most specific level to the most general. You are doing it the other way round.

Without a good model, you'll be facing issues constantly that should not be there in the first place. This will lead to complex and hardly maintainable DAX. Do not go this route if you want to stay sane. Please learn the rules of correct data modelling (star/snowflake schema).

Best
D