cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

hi @Sean and @Anonymous

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

@Anonymous I think this should work!

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

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

Anonymous
Not applicable

@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

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

Anonymous
Not applicable

Thanks @Sean

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

Anonymous
Not applicable

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?

Anonymous
Not applicable

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.

Community Champion

@KHorseman @Vvelarde Can you do % of Row Total so the % adjusts to 100% even after you use Slicers from a related table?

Current Measure

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

Here's the Sample Data I created

Table 1

 Item ID Item Description Quarter Sales Customer A Category 1 Q1 50 Customer 1 A Category 2 Q1 50 Customer 2 A Category 1 Q2 150 Customer 2 A Category 2 Q2 150 Customer 3 A Category 1 Q3 100 Customer 3 A Category 2 Q3 100 Customer 1 A Category 1 Q4 200 Customer 1 A Category 2 Q4 200 Customer 4 B Category 1 Q1 250 Customer 1 B Category 2 Q1 250 Customer 2 B Category 1 Q2 0 Customer 2 B Category 2 Q2 0 Customer 3 B Category 1 Q3 250 Customer 3 B Category 2 Q3 250 Customer 1 B Category 1 Q4 0 Customer 1 B Category 2 Q4 0 Customer 4 C Category 1 Q1 0 Customer 1 C Category 2 Q1 0 Customer 2 C Category 1 Q2 125 Customer 2 C Category 2 Q2 125 Customer 3 C Category 1 Q3 0 Customer 3 C Category 2 Q3 0 Customer 1 C Category 1 Q4 375 Customer 1 C Category 2 Q4 375 Customer 4 D Category 1 Q1 0 Customer 1 D Category 2 Q1 0 Customer 2 D Category 1 Q2 0 Customer 2 D Category 2 Q2 0 Customer 3 D Category 1 Q3 0 Customer 3 D Category 2 Q3 0 Customer 1 D Category 1 Q4 500 Customer 1 D Category 2 Q4 500 Customer 4

Table 2

 Customer Customer Type Region Customer 1 Govt USA Customer 2 Non Govt USA Customer 3 Govt Canada Customer 4 Non Govt Canada
Community Champion

hi @Sean and @Anonymous

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
Regular Visitor

Hi

I need some help..

I have a table

Date(Bins)    Type A        Type B        Type C       RowTotal (I created this measure adding the row)

Jan 2016         30              276              342               648

Feb 2016         24             269              307               600

Mar 2016        41             332              286               659

I need to be able to add columns Type A % of RowTotal

Type B % of Row Total

Type C % of Row Total

and display that in 3 columns for each of the months or rows like shown above

Regular Visitor

Hi

I need some help..

I have a table

Date(Bins)    Type A        Type B        Type C       RowTotal (I created this measure adding the row)

Jan 2016         30              276              342               648

Feb 2016         24             269              307               600

Mar 2016        41             332              286               659

I need to be able to add columns Type A % of RowTotal

Type B % of Row Total

Type C % of Row Total

and display that in 3 columns for each of the months or rows like shown above

Community Champion

@Vvelarde Great job!

Looks like this should do the job @Anonymous ?

Frequent Visitor

I am struggling the with a similar issue.

I have per day sales over a month and want the weekly % total as well as the % of the month.

If I wanted to slice the months data down to the week of the month will these measures still work?

I was using this as a reference but not sure that results will be what I am looking for:

http://databear.com/2016/05/26/power-bi-tips-calculate-one-of-the-most-used-dax-functions/

Anonymous
Not applicable

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.