cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
omitche2 Frequent Visitor
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

Accepted Solutions
omitche2 Frequent Visitor
Frequent Visitor

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

instead of creating calculated columns i had to create measures

View solution in original post

3 REPLIES 3
tsantandreu Member
Member

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

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

omitche2 Frequent Visitor
Frequent Visitor

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

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. 

omitche2 Frequent Visitor
Frequent Visitor

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

instead of creating calculated columns i had to create measures

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors