Forum Discussion
How to Combine Counts & Percentage Difference into one visual
Hi moonpie ,
I recommend to use a Line and clustered column chart to combine Count and Percentage Difference together in the same visual while Count for column values and Percentage Difference for line values. Besides, you don’t need to create measures for each year. One measure will do it for all years in the same visual. Please take below steps for reference.
First, create a new column to get Year value:
Year = VALUE(RIGHT('Fall'[Term],4))Then, create 2 measures like below:
Total count = COUNT('Fall'[ID]) % Diff = VAR currentYear = MAX(Fall[Year]) VAR previousCount = CALCULATE([Total count],ALL(Fall[Year]),Fall[Year] = currentYear - 1) RETURN DIVIDE([Total count] - previousCount, previousCount)Finally, create the visual using a Line and clustered column chart. You can use the Day slicer to display a different day.
Best Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
moonpie Do you want the result like this?
I tested my % Diff measure and it works well as above, so could you please check again with it? Do you make any modifications to it?
There is no need to create two measures for the chart to display % Diff because it will calculate the value for each year dynamically according to Year. You could create a table like below to test this.
Additionally, if you want to use different measures to display % Diff 18-19 and % Diff 19-20 in the same table just like that in the first image, I used measures like below:
Total 2018 = CALCULATE([Total count],ALL(Fall[Year]),Fall[Year]=2018) Total 2019 = CALCULATE([Total count],ALL(Fall[Year]),Fall[Year]=2019) Total 2020 = CALCULATE([Total count],ALL(Fall[Year]),Fall[Year]=2020) % Diff 18-19 = DIVIDE([Total 2019] - [Total 2018], [Total 2018]) % Diff 19-20 = DIVIDE([Total 2020] - [Total 2019], [Total 2019])Hope this will be helpful.
Hi v-jingzhang
NOTE: I deleted my previous reply. I realized I had made a mistake in my %Diff 18-19 measure, and that is why it wasn't matching. Now it matches. I tried updating my previous reply with screenshots but it was giving me problems adding those updated screenshots.
THANK YOU FOR REPLYING!! It is greatly appreciated.
I created the % DIFF measure you gave me and it did work for part of what I am trying to do, which was find the % difference between year 2019 and year 2020.
As you see the calculated measure matches my own calculated measure (highlighted values).
There is another percent difference I am trying to find which is between the year 18 and year 19.
I took the measure that you had given me, and created a modified measure to try to accomplish this.
% Diff 2 =
VAR FirstYear = MIN(Fall[Year])
Var SecondYr = FirstYear + 1
VAR SecondCount = CALCULATE([Total count],ALL(Fall[Year]),Fall[Year] = SecondYr )
VAR FirstCount = CALCULATE([Total count],ALL(Fall[Year]),Fall[Year] = FirstYear)
RETURN
DIVIDE(SecondCount-FirstCount,FirstCount)
And the results matches my % DIFF 18-19 column, so I know that I have now created % DIFF 2 correctly.
However when I add that new measure to the line Values, it plots 3 points on the graph.
I was hoping that it would plot similar to how %DIFF one was plotted, going from 2018 to 2019.
I am thinking that maybe I didn't write my % Diff 2 measure correctly.
Would you be able to assist me with this?
Thank you,
moonpie
Community Supportmoonpie Do you want the result like this?
I tested my % Diff measure and it works well as above, so could you please check again with it? Do you make any modifications to it?
There is no need to create two measures for the chart to display % Diff because it will calculate the value for each year dynamically according to Year. You could create a table like below to test this.
Additionally, if you want to use different measures to display % Diff 18-19 and % Diff 19-20 in the same table just like that in the first image, I used measures like below:
Total 2018 = CALCULATE([Total count],ALL(Fall[Year]),Fall[Year]=2018)
Total 2019 = CALCULATE([Total count],ALL(Fall[Year]),Fall[Year]=2019)
Total 2020 = CALCULATE([Total count],ALL(Fall[Year]),Fall[Year]=2020)
% Diff 18-19 = DIVIDE([Total 2019] - [Total 2018], [Total 2018])
% Diff 19-20 = DIVIDE([Total 2020] - [Total 2019], [Total 2019])
Hope this will be helpful.
Yes, There was a slight mistake in my measure.
Thank you very much for your assistance. It is greatly appreciated!