cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

Calculate percent of row in a table

Hello,

 

I have a large matrix set up and would like to set up a measure that calculates the total row percentage. ie:

 

Item   Qtr1   Qtr2   Qtr3   Qtr4   Total

A        100    300     200    400     1000

 

The measure would calculate as 

 

Item   Qtr1   Qtr2   Qtr3   Qtr4   Total

A        10%    30%   20%   40%    100%

 

I also have a handful of page level filters that I have to account for. I tried 

%ofTotal = DIVIDE(SUM(Table[Sales]),CALCULATE(SUM(Table[Sales]), ALLEXCEPT(Table,Table[Sales]

This returns a percentage of total sales for the entire table. Please help.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Champion
Community Champion

hi @Sean and @rynoh17

 

To calculate the % for each item this is the dax sentence:

 

% of Total Measure =
DIVIDE (
    CALCULATE ( SUM ( Table1[Sales] ) ),
    CALCULATE (
        SUM ( Table1[Sales] ),
        ALL ( Table1[Quarter], Table1[Item Description] )
    ),
    0
)

img1.png

 

And if you want 100% for each Item Description.

 

% of Total Measure =
DIVIDE (
    CALCULATE ( SUM ( Table1[Sales] ) ),
    CALCULATE ( SUM ( Table1[Sales] ), ALL ( Table1[Quarter] ) ),
    0
)

img2.png




Lima - Peru

View solution in original post

16 REPLIES 16
Community Champion
Community Champion

@rynoh17 I think this should work! Smiley Happy

 

% of Total Measure =
DIVIDE (
    SUM ( 'Table'[Sales] ),
    CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Item] ) ),
    0
)

@Sean

 

Thanks for the responsse. That didn't do what I wanted it to though.

 

Capture.PNG

 

Here is a snippet of the return. The final % of Total should be 100% for every row. the first % of Total should be ($3,632.20/$1,743,851.20)=0.2%, not 0.08% as shown. Would this have to do  with my report level filters?

Community Champion
Community Champion

How many fields do you have in the Rows of the Matrix and in what order and which one are you using to filter?

@Sean

 

Two fields, Item ID and Item description. Neither of those is filtered. My filters are report level.

Customer type: Not a National or government account
Region: Just US and CAN regions

Community Champion
Community Champion

@rynoh17 its noon here and i have to go - but I'm sure @KHorseman can help you with this!

 

The order of the fields matters (I was going by your original sample)

 

2016-10-24 - % of Row Total.png

Thanks @Sean

 

Yeah, I was just trying to generalize and simplify without using sensetive data.

Hey @Sean,

 

Any idea of how to get what I am looking for to work?

Community Champion
Community Champion

So how are Customer Type and Region related to ItemID and Item Desciption?

So this is data at the transactional level. Every customer has a Tier and are in a certain region. Item ID and Description are based off that particular sale to that particular customer.

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors