Forum Discussion
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 %]
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
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.