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
NidhiBhusari
Helper IV
Helper IV

Calculate % of Total and Cumulative %

Hello All,

I want to calculate % of Total & Cumulative % for the fields in the matrix. As we drill down or drill up i.e from Category --> SubCategory or vice versa the values should be shown accordingly. It will be really helpful if someone can suggest the logic for the same. I am using a sample superstore dataset.

Regards,
Nidhi

NidhiBhusari_0-1657256032596.png

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

I hope the below can provide some ideas on creating a solution for your data model.

 

Untitled.png

 

 

Sales measure: = 
SUM( Sales[Sales] )

 

Sales cumulate percentage: = 
VAR _sales = [Sales measure:]
VAR _subcategorycumulatesales =
    CALCULATE (
        [Sales measure:],
        FILTER (
            ALL ( Category ),
            Category[Category] = MAX ( Category[Category] )
                && [Sales measure:] >= _sales
        )
    )
VAR _subcategoryallsales =
    CALCULATE ( [Sales measure:], ALLEXCEPT ( Category, Category[Category] ) )
VAR _categorycumulatesales =
    CALCULATE (
        [Sales measure:],
        FILTER ( ALL ( Category[Category] ), [Sales measure:] >= _subcategoryallsales )
    )
VAR _categoryallsales =
    CALCULATE ( [Sales measure:], ALL ( Category ) )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( Category[Subcategory] ), DIVIDE ( _subcategorycumulatesales, _subcategoryallsales ),
        DIVIDE ( _categorycumulatesales, _categoryallsales )
    )
Sales percentage: = 
VAR _sales = [Sales measure:]
VAR _subcategoryallsales =
    CALCULATE ( [Sales measure:], ALLEXCEPT ( Category, Category[Category] ) )
VAR _categoryallsales =
    CALCULATE ( [Sales measure:], ALL ( Category ) )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( Category[Subcategory] ), DIVIDE ( _sales, _subcategoryallsales ),
        DIVIDE ( _subcategoryallsales, _categoryallsales )
    )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

I hope the below can provide some ideas on creating a solution for your data model.

 

Untitled.png

 

 

Sales measure: = 
SUM( Sales[Sales] )

 

Sales cumulate percentage: = 
VAR _sales = [Sales measure:]
VAR _subcategorycumulatesales =
    CALCULATE (
        [Sales measure:],
        FILTER (
            ALL ( Category ),
            Category[Category] = MAX ( Category[Category] )
                && [Sales measure:] >= _sales
        )
    )
VAR _subcategoryallsales =
    CALCULATE ( [Sales measure:], ALLEXCEPT ( Category, Category[Category] ) )
VAR _categorycumulatesales =
    CALCULATE (
        [Sales measure:],
        FILTER ( ALL ( Category[Category] ), [Sales measure:] >= _subcategoryallsales )
    )
VAR _categoryallsales =
    CALCULATE ( [Sales measure:], ALL ( Category ) )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( Category[Subcategory] ), DIVIDE ( _subcategorycumulatesales, _subcategoryallsales ),
        DIVIDE ( _categorycumulatesales, _categoryallsales )
    )
Sales percentage: = 
VAR _sales = [Sales measure:]
VAR _subcategoryallsales =
    CALCULATE ( [Sales measure:], ALLEXCEPT ( Category, Category[Category] ) )
VAR _categoryallsales =
    CALCULATE ( [Sales measure:], ALL ( Category ) )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( Category[Subcategory] ), DIVIDE ( _sales, _subcategoryallsales ),
        DIVIDE ( _subcategoryallsales, _categoryallsales )
    )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


amitchandak
Super User
Super User

@NidhiBhusari , based on what I got example

 

 Cumm Sales % = divide( CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date]))), CALCULATE(SUM(Sales[Sales Amount]) , all()) )

or


Cumm Sales % = divide( CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date]))), CALCULATE(SUM(Sales[Sales Amount]) , allselected()) )

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.