Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I need to do a calculation of percent change from percentage results
the first layer was to calculate the percent mix per column on a year to year basis example:
2018_YTD = CALCULATE(SUM('Table1'[Value number]),DATESBETWEEN('Table'[Value date],date(2018,1,1),DATE(2018,MONTH(TODAY()),day(TODAY()))))
2019_YTD = CALCULATE(SUM('Table'[Value number]),'Table'[Field with year number]=2019)
These two results, I dragged and dropped in the Value field in the vizualization pane, and selected show value as a percent of column total to obtain the results as a %
then, Created this other formula between the two years, but this results show only the growth from 2018 into 2019.
% Chg = Divide('Table'[2019_YTD],'Table'[2018_YTD])-1
What I need is the change between the percentages see below
This is the result I need | 2018 | 2019 | % change between years: |
Owner | 28.20% | 30.56% | 8.4% (this is = (30.56%/28.20%) - 1 |
Tenant | 71.80% | 69.44% | -3.3% |
DAX formula gives | |||
2018 | 2019 | ||
Owner | 1497 | 2583 | 73% |
Tenant | 3811 | 5868 | 54% |
Solved! Go to Solution.
Hi @Anonymous ,
To create measures as below.
2018_YTD = CALCULATE(SUM('Table1'[Value number]),DATESBETWEEN(Table1[date],date(2018,1,1),DATE(2018,MONTH(TODAY()),day(TODAY()))))
2019_YTD = CALCULATE(SUM(Table1[Value number]),Table1[Field with year number]=2019)
2018 % = DIVIDE([2018_YTD],SUMX(ALLSELECTED(Table1[role]),[2018_YTD]))
2019 % = DIVIDE([2019_YTD],SUMX(ALLSELECTED(Table1[role]),[2019_YTD]))
% change between years = DIVIDE([2018 %],[2019 %])-1
Hi @Anonymous ,
To create measures as below.
2018_YTD = CALCULATE(SUM('Table1'[Value number]),DATESBETWEEN(Table1[date],date(2018,1,1),DATE(2018,MONTH(TODAY()),day(TODAY()))))
2019_YTD = CALCULATE(SUM(Table1[Value number]),Table1[Field with year number]=2019)
2018 % = DIVIDE([2018_YTD],SUMX(ALLSELECTED(Table1[role]),[2018_YTD]))
2019 % = DIVIDE([2019_YTD],SUMX(ALLSELECTED(Table1[role]),[2019_YTD]))
% change between years = DIVIDE([2018 %],[2019 %])-1
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |