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

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

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

@Anonymous I think this should work! Smiley Happy

 

% 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.

 

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?

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

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

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

 

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

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?

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

Sean
Community Champion
Community Champion

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

 

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

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 IDItem DescriptionQuarterSalesCustomer
ACategory 1Q150Customer 1
ACategory 2Q150Customer 2
ACategory 1Q2150Customer 2
ACategory 2Q2150Customer 3
ACategory 1Q3100Customer 3
ACategory 2Q3100Customer 1
ACategory 1Q4200Customer 1
ACategory 2Q4200Customer 4
BCategory 1Q1250Customer 1
BCategory 2Q1250Customer 2
BCategory 1Q20Customer 2
BCategory 2Q20Customer 3
BCategory 1Q3250Customer 3
BCategory 2Q3250Customer 1
BCategory 1Q40Customer 1
BCategory 2Q40Customer 4
CCategory 1Q10Customer 1
CCategory 2Q10Customer 2
CCategory 1Q2125Customer 2
CCategory 2Q2125Customer 3
CCategory 1Q30Customer 3
CCategory 2Q30Customer 1
CCategory 1Q4375Customer 1
CCategory 2Q4375Customer 4
DCategory 1Q10Customer 1
DCategory 2Q10Customer 2
DCategory 1Q20Customer 2
DCategory 2Q20Customer 3
DCategory 1Q30Customer 3
DCategory 2Q30Customer 1
DCategory 1Q4500Customer 1
DCategory 2Q4500Customer 4

 

Table 2

CustomerCustomer TypeRegion
Customer 1GovtUSA
Customer 2Non GovtUSA
Customer 3GovtCanada
Customer 4Non GovtCanada
Vvelarde
Community Champion
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
)

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

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

 

Anyhelp? Thanks in advance

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

 

Anyhelp? Thanks in advance

Sean
Community Champion
Community Champion

@Vvelarde Great job! Smiley Happy

 

Looks like this should do the job @Anonymous ?

fyrworx
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.

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.