Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
moonpie
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 

moonpie_1-1600882244309.png

 

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.

moonpie_2-1600882391795.png

 

moonpie_3-1600882447136.png

 

And then appending those 3 appended tables into 1 table.

 

moonpie_4-1600882515167.png

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.

 

moonpie_0-1600884703323.png

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. 

 

moonpie_0-1600898459025.png

 

 

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.

 

Thank you in advance.  

 



 

 

 

1 ACCEPTED SOLUTION

@moonpie Do you want the result like this?

v-jingzhang_0-1601274840174.jpeg

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.

v-jingzhang_1-1601278054282.png

 

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.

View solution in original post

4 REPLIES 4
v-jingzhang
Community Support
Community Support

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))

v-jingzhang_0-1601027631286.png

 

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.

v-jingzhang_1-1601027631292.png

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.

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).

 

moonpie_0-1601054671490.png

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.

 

moonpie_2-1601054980029.png

However when I add that new measure to the line Values, it plots 3 points on the graph. 

 
 
 

moonpie_3-1601055053173.png

 

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

@moonpie Do you want the result like this?

v-jingzhang_0-1601274840174.jpeg

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.

v-jingzhang_1-1601278054282.png

 

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.