cancel
Showing results for
Did you mean:
Frequent Visitor

## How to Combine Counts & Percentage Difference into one visual

Good afternoon.

So this will be a lengthy post, only because I am trying to explain myself as much as possible.

I have searched the Power BI community and I couldn't find something that will fit what I am hoping to do.

I apologize in Advance for any wrong terminology used.

Just a bit of background: So I will be working with Enrollment data. I created sample data (no real data is being used) that more or less replicates what I will be working with.

I have 12 Excel sheets of sample data:

• Fall 2020
• Day 1
• Day 2
• Day 3
• Day 4
• Fall 2019
• Day 1
• Day 2
• Day 3
• Day 4
• Fall 2018
• Day 1
• Day 2
• Day 3
• Day 4

All Excel sheets have the Following Columns:

• Sample ID
• Term
• Day I imported the files, then ended up grouping files together based on the term and then appending files together into one table based on the term.  And then appending those 3 appended tables into 1 table. So one of the things I was working on was creating the bar chart inorder to view enrollment numbers based on which day is selected in the filter, which is shown below. Now I know that one of the tasks I will be assigned is to find the percentage difference between Fall 2020 and Fall 2019 & Fall 2019 and Fall 2018 and to include those differences in the same visual as the counts.

I created different measures (not sure if the way I did them was the best way to do it):

Total Count = count(Fall[ID])
Total Fall 2018 = CALCULATE([Total Count],Fall[Term]="Fall 2018")
Total Fall 2019 = CALCULATE([Total Count],Fall[Term]="Fall 2019")
Total Fall 2020 = CALCULATE([Total Count],Fall[Term]="Fall 2020")
DIFF 19-20 = [Total Fall 2020]-[Total Fall 2019]
DIFF 18-19 = [Total Fall 2019]-[Total Fall 2018]
% DIFF 19-20 = ([Total Fall 2020]-[Total Fall 2019])/[Total Fall 2019]
% DIFF 18-19 = ([Total Fall 2019]-[Total Fall 2018])/[Total Fall 2019]

And when I tried adding the new measures to the chart it ends up sort of duplicating the measure for each term. So I was wondering if someone knew how I would be able to combine percentages with counts in the bar graph or if someone knew of a visual to allow me to show bouth  both counts and percentages in a graph/chart format.

1 ACCEPTED SOLUTION Microsoft

@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])``````

4 REPLIES 4 Microsoft

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.

Frequent Visitor

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 Microsoft

@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])``````

Frequent Visitor

Yes, There was a slight mistake in my measure.

Thank you very much for your assistance. It is greatly appreciated!   