cancel
Showing results for
Did you mean:
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]`

1 ACCEPTED SOLUTION

Accepted Solutions
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
)```

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

Lima - Peru
16 REPLIES 16
Community Champion

@rynoh17 I think this should work!

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

@Sean

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

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

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

Helper IV

@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

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

Helper IV

Thanks @Sean

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

Helper IV

Hey @Sean,

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

Community Champion

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

Helper IV

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

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