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
Tsch
New Member

Trend over time expressed as percentage of total volume over time periods

Greeting all,

I need help through a mental block. I am sure it is a couple of measures that need to be involved here but cannot seem to find the correct path.

I need to create a line chart showing trends over time with values shown as a percentage of total of that time period (quarter, month). Using the % of grand total shows a total of the relevant time period not the percentage of what it was that quarter. The time period total count varies between time periods.

 What are the correct measures to use here? There has to be a count performed since they are individual rows. So the final measure would be something like =COUNT (fruit) / (Quarterly Volume)

Each row is a single occurence in the data. For example:

RegionQuarterFruit
11Apples
12Oranges
13Pears
14Grapes
21Apples
22Oranges
23Pears
24Grapes
31Apples
32Oranges
33Pears
34Grapes
41Apples
42Oranges
43Pears
44Grapes

It should appear like this except each point is a % of quarterly total.

fruit.jpg

Thanks!

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

@Tsch

 

Hi,

Maybe this is the measure.

Measure =

COUNT ( Table45[Fruit] )

/ CALCULATE ( COUNT ( Table45[Fruit] ), ALLEXCEPT ( Table45, Table45[Quarter] ) )Trend over time expressed as percentage of total volume over time periods.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-jiascu-msft
Employee
Employee

@Tsch

 

Hi,

Maybe this is the measure.

Measure =

COUNT ( Table45[Fruit] )

/ CALCULATE ( COUNT ( Table45[Fruit] ), ALLEXCEPT ( Table45, Table45[Quarter] ) )Trend over time expressed as percentage of total volume over time periods.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Dale,

 

You got it! Works as I would expect. Now to go educate myself a bit more on why that allexcept function works...

Thanks!

 

tsch

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.