cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
NJ23 Frequent Visitor
Frequent Visitor

Need help calculation difference between two % of CT Columns

Hi,

 

I have been struggling with calculating the difference between two values represented as % of column totals to find the % change that works dynamically. Hopefully through these mock tables the issue I'm having will be clear. My base data is drug class and drug, with the quantity of drugs filled at each year being the metrics I need to work with:

 

Drug ClassDrugYear 1 QuantityYear 2 Quantity
Class ADrug 1700600
Class ADrug 2500700
Class ADrug 3700700
Class ADrug 4300100
Class BDrug 51000900
Class CDrug 610001100

 

Using show value as % of column total, I get exactly what I want to show which re-calculates as the % of the class on drill down:

 

Drug Class% CT Year 1% CT Year 2
Class A52.38%51.22%
Class B23.81%21.95%
Class C23.81%26.83%
Total100%100%

 

Drug Class% CT Year 1% CT Year 2
Class A100%100%
Drug 131.82%28.57%
Drug 222.73%33.33%
Drug 331.82%33.33%
Drug 413.64%4.76%
Total100%100%

 

What I am having difficultly trying to calculate is the difference between these in a new column that works dynamically. The % change between the classes and the % change of the drugs within the class. I'm not concerned with what the "total" column would show in this at all but this is how I'd want the data to look for the drugs and classes:

 

Drug Class% CT Year 1% CT Year 2New Column
Class A52.38%51.22%-1.16%
Class B23.81%21.95%-1.86%
Class C23.81%26.83%3.02%
Total100%100%0%

 

Drug Class% CT Year 1% CT Year 2New Column
Class A100%100%0%
Drug 131.82%28.57%-3.25%
Drug 222.73%33.33%10.60%
Drug 331.82%33.33%1.51%
Drug 413.64%4.76%-8.88%
Total100%100%0.00%

 

Any help with this would be greatly appreciated. I've tried to calculate the %CT funtion myself using DAX that works dynamically so I could use it in the new column calc but thats been fruitless so far. Any proposed solution would be greatly appreciated.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
omitche2 Frequent Visitor
Frequent Visitor

Re: Need help calculation difference between two % of CT Columns

You need to create measures to make this work dynamically when you drill up or down.

 

ie

measure Year 1 Quantity total = calculate(sum(table[Year 1 Quantity ]),ALLSELECTED())

measure Year 2 Quantity total = calculate(sum(table[Year 2 Quantity ]),ALLSELECTED())

 

Year 1 % = divide(sum(table[Year 1 Quantity]),[measure Year 1 Quantity total])
Year 2 % = divide(sum(table[Year 2 Quantity]),[measure Year 2 Quantity total])
 
% diff = [Year 2 %]-[Year 1 %]
 
 

 

 

View solution in original post

3 REPLIES 3
amitchandak Super Contributor
Super Contributor

Re: Need help calculation difference between two % of CT Columns

You can create this year and last year total like anyone these

 

Year  = CALCULATE(SUM(table[column]),DATESYTD('Date'[Date Filer]))
Last Year  = CALCULATE(SUM(table[column]),DATESYTD(dateadd('Date'[Date Filer],-12,MONTH)))


Year Sales = CALCULATE(TOTALYTD(sum(Sales[Sales Amount]),('Date'[Date Filer])))
Last Year Sales = CALCULATE(TOTALYTD(sum(Sales[Sales Amount]),dateadd('Date'[Date Filer].-12,month)))


Year  = CALCULATE(SUM(table[column]),DATESYTD(endofmonth(dateadd('Date'[Date Filer],-1,MONTH))))
Last Year  = CALCULATE(SUM(table[column]),DATESYTD(endofmonth(dateadd('Date'[Date Filer],-13,MONTH))))


Year Sales = CALCULATE(TOTALYTD(sum(Sales[Sales Amount]),endofmonth(dateadd('Date'[Date Filer].-1,month))))
Last Year Sales = CALCULATE(TOTALYTD(sum(Sales[Sales Amount]),endofmonth(dateadd('Date'[Date Filer].-13,month))))

 

Add additional filter of all(Table). That should give you GT.

 

Then use the divide function. to get % of the total and the calculate change %.

 

Another way. Check Quick formula. There are options to create some of these things quickly. Right-click on a field or measure.

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

 

 

NJ23 Frequent Visitor
Frequent Visitor

Re: Need help calculation difference between two % of CT Columns

@amitchandak  I don't think this works on the drill down. The issue I am having is I need % of CT, so the denominator would be the sum of the drug class at the drug level and would be the sum of all table at the class level.

omitche2 Frequent Visitor
Frequent Visitor

Re: Need help calculation difference between two % of CT Columns

You need to create measures to make this work dynamically when you drill up or down.

 

ie

measure Year 1 Quantity total = calculate(sum(table[Year 1 Quantity ]),ALLSELECTED())

measure Year 2 Quantity total = calculate(sum(table[Year 2 Quantity ]),ALLSELECTED())

 

Year 1 % = divide(sum(table[Year 1 Quantity]),[measure Year 1 Quantity total])
Year 2 % = divide(sum(table[Year 2 Quantity]),[measure Year 2 Quantity total])
 
% diff = [Year 2 %]-[Year 1 %]
 
 

 

 

View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)