calculating Percent% in Matrix Hierarchies

[Anonymous]

Can some one tell me what I am missing , below is my DAX , I don't want 100% at the ID level , should be 5.2%, 4.1 % and son when drill down to see ID %

Sales % = FORMAT( IF (

ISFILTERED (Stores[Brand]),

SUM (Sales[Sales])

/

CALCULATE (

SUM (Sales[Sales]),

ALL (Stores[Brand])

),

IF (

ISFILTERED (Stores[Store ID]),

SUM (Sales[Sales])

/

CALCULATE (

SUM (Sales[Sales])

))) , "Percent")

[Wvnassau]

Hi, 

I have created a sum of values over three hierarchies in a matrix via isinscope. Great tip, thanks. 

The measure works, however, when I utilitize it as a tooltip on the matrix, isinscope seems to result in a never in scope. Where it should give me the percent of total per hierarcy, it now always gives 100%. 

This is my measure now:

Bijdrage =

var total_m = calculate([sum], ALLSELECTED(fct[m]))

var total_t = calculate([sum], ALLSELECTED(fct[t]))

var total_s= CALCULATE([sum], ALLSELECTED(fct[s]))

var total = calculate([sum], ALLSELECTED(fct))

var result =

switch(true(),

    ISINSCOPE(fct[m]), DIVIDE([sum], total_m),

    ISINSCOPE( fct[t]), DIVIDE([sum], total_t),

    ISINSCOPE(fct[s]), DIVIDE ([sum], total_s),

    divide([sum], total) )

return result

Can anyone help me with making my tooltip deliver similar results? 🙂 Appreciated!

[harshnathani]

Hi @Anonymous ,

Try something like this

Sales % =
SWITCH (
    TRUE (),
    ISINSCOPE ( Brand[ID] ), FORMAT (
        DIVIDE (
            CALCULATE (
                [Total Sales],
                ALLEXCEPT (
                    Brand,
                    Brand[ID]
                )
            ),
            CALCULATE (
                [Total Sales],
                ALLEXCEPT (
                    Brand,
                    Brand[Brand Name]
                )
            )
        ),
        "Percent"
    ),
    [Total Sales]
)

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[Anonymous]

@harshnathani  this doesn't work , first of all in my matirx  first column is sales  dollar value and second is percentage , first column is already working well, second column has the issue , when drill down the hierarchy Id level is showing 100% , puting on your calc creating one column and showing percent under dollar value  liek below 

what I want is some thing like this 

[AntrikshSharma]

Try this. The percentage at brand level is high because I don't have your full data.

TotalSales = SUM ( Khana[Sales] )

Sales Perc = 
VAR TotalID =
    CALCULATE ( [TotalSales], ALLSELECTED ( Khana[ID] ) )
VAR GrandTotal =
    CALCULATE ( [TotalSales], ALLSELECTED ( Khana ) )
VAR Result =
    IF (
        ISINSCOPE ( Khana[ID] ),
        DIVIDE ( [TotalSales], TotalID ),
        IF ( ISINSCOPE ( Khana[Brand] ), DIVIDE ( [TotalSales], GrandTotal ), 1 )
    )
RETURN
    Result

[harshnathani]

Hi @Anonymous ,

unclear as to what is needed.

I tried something on dummy data.

Check if this works for you else share some more clarity on your requirement.

Sales % =
VAR a =
    CALCULATE (
        [Total Sales],
        ALLEXCEPT (
            Geography,
            Geography[City]
        )
    )
VAR b =
    CALCULATE (
        [Total Sales],
        ALLEXCEPT (
            Geography,
            Geography[State]
        )
    )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( Geography[City] ), DIVIDE (
            a,
            b
        ),
        DIVIDE (
            b,
            CALCULATE (
                [Total Sales],
                ALL ( Sales )
            )
        )
    )

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[Anonymous]

The total of ID should Mach up to 14.43% so I am getting osme calculation but the numbers are wrong

[Greg_Deckler]

@Anonymous - Check out MM3TR&R - https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-Roll/m-p/411443#M150

[amitchandak]

@Anonymous , refer if this can help

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

https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/