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
omitche2
Frequent Visitor

Looking for the dax for, "show value as percent of column total"

Hi, 

 

I have a table with a lot of data that has columns for sales in 2018 and 2019 by region and store ID.

I want to present the data for sales in a matrix with the ability to drill down from the Region to store ID and drill back up.

I want to be able to see the sales volume as a percent of the column total for 18 and 19 when I'm drilled up or down.

I want to show the percentage difference between 18 and 19.

 

I can achieve everything except the percentage difference between 18 and 19. 

 

Step 1This sample matrix shows the sum of sales as percent of column total 
 but I am unable to calc the difference b/c these values don't exist in the table 
      
 REGIONSALES '18SALES '19%Change 
 NORTH18%29%  
 SOUTH32%40%  
 EAST25%17%  
 WEST25%15%  

 

Step 2Drill down on North, the 18% of sales is converted to 100% 
 and shows the breakout for that region by store id as a percent of column total
 Again, I can' t show the percentage difference 
     
 REGIONSALES '18SALES '19%Change
 NORTH100%100% 
 13520%22% 
 11925%29% 
 21465%70% 

 

Problems: 

With the values for the sum of sales for 18 & 19 shown as % of column total, I can't simply subtract 19 from 18.

I don't have the dax calculation for the, "show value as" calc, so I can't create calculated columns and then subtract 1 from the other.

 

I tried: 

Calculating the first step % change by creating new calculated columns and then subtracting them.

Sales '18 = Query1[sales_18]/calculate(sum(Query1[sales_18]),ALLSELECTED())

Sales '19 = Query1[sales_19]/calculate(sum(Query1[sales_19]),ALLSELECTED())

%Change=[Sales '19]-[Sales '18]

 

That worked until I drilled down...

this is right   
REGIONSALES '18SALES '19%Change
NORTH18%29%11%
SOUTH32%40%8%
EAST25%17%-8%
WEST25%15%-10%

 

when I drilled down it gave me this:

REGIONSALES '18SALES '19%Change
NORTH18%29%11%
1357%8%1%
1198%10%2%
2143%11%8%

 

I want this:

REGIONSALES '18SALES '19%Change
NORTH100%100%11%
13539%28%-11%
11944%34%-10%
21417%38%21%

 

 

1 ACCEPTED SOLUTION

instead of creating calculated columns i had to create measures

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi

 

You can try the following:

Year 18 = divide(sum(Query1[sales_18]),calculate(sum(Query1[sales_18]),ALLSELECTED()))
Year 19 = divide(sum(Query1[sales_19]),calculate(sum(Query1[sales_19]),ALLSELECTED()))
% Change = [Year 19] - [Year 18]

 

Let me know if it works for you.

Tomas

No that didn't work - the calc I'm looking to replicate is what ever is behind the, "show value as a percent of column total".   When I drill down in the matrix that calc works dynamically.  Instead of calcing based on the total sales it looks specifically at the total for the region. 

instead of creating calculated columns i had to create measures

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.

Top Solution Authors