Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

calculating Percent% in Matrix Hierarchies

khana04_0-1595545565263.png

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

 

8 REPLIES 8
Wvnassau
Regular Visitor

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
Community Champion
Community Champion

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
Not applicable

@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 image.png

 

what I want is some thing like this 

image.png

 

 

 

 

 

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

1.PNG

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

 

1.jpg

 

 

Regards,
Harsh Nathani

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

 

 

Anonymous
Not applicable

khana04_0-1595571592262.png

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

Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors