cancel
Showing results for
Did you mean:
Highlighted
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 1 This 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 REGION SALES '18 SALES '19 %Change NORTH 18% 29% SOUTH 32% 40% EAST 25% 17% WEST 25% 15%

 Step 2 Drill 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 REGION SALES '18 SALES '19 %Change NORTH 100% 100% 135 20% 22% 119 25% 29% 214 65% 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 REGION SALES '18 SALES '19 %Change NORTH 18% 29% 11% SOUTH 32% 40% 8% EAST 25% 17% -8% WEST 25% 15% -10%

when I drilled down it gave me this:

 REGION SALES '18 SALES '19 %Change NORTH 18% 29% 11% 135 7% 8% 1% 119 8% 10% 2% 214 3% 11% 8%

I want this:

 REGION SALES '18 SALES '19 %Change NORTH 100% 100% 11% 135 39% 28% -11% 119 44% 34% -10% 214 17% 38% 21%

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

3 REPLIES 3
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

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.

Frequent Visitor

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

Announcements

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

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

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

Top Solution Authors
Top Kudoed Authors