topic Re: Plot average by legend category against time in DAX Commands and Tips

Plot average by legend category against time

oliverblane — Thu, 23 Dec 2021 17:57:33 GMT

I have a table that looks like the following:

I am currently plotting this data over time with a switch that allows the user to change whether the legend is Location Category, Content Provider, or Lot. An example of this is shown below:

So if I was to click Lot on the slicer, then the plot would compare Lots instead.

However, I would like to modify this plot so that each unique category is calculated as an average per establishment. For example, when using Location Category as the legend, I would like to have each monthly value for Location A to be divided by 5 (since there are 5 establishments with Location = A), each monthly value for Location B to be divided by 3, and each monthly value for Location C to be divided by 2. The same should apply when using the Lot or Content Provider as the legend.

Here is a link to the PBIX file: https://meganexuslimited-my.sharepoint.com/:u:/g/personal/oliver_blane_meganexus_com/EdHNHYsYxGFOllte8yd7F1EB3CWyKW-lusM3Cl8U9y7pRQ?e=EOXhKV

Thanks in advance for any help I might get on this!

Re: Plot average by legend category against time

PaulOlding — Fri, 24 Dec 2021 10:34:14 GMT

Hi oliverblane

The number of establishments in the current context is going to be

COUNTROWS(VALUES(Table[Establishment]))

So, you could have new measures that's a division of your existing measures by the above. Something like:

Average Per Establishment = VAR _Num = [Existing Measure] VAR _Denom = COUNTROWS(VALUES(Table[Establishment])) VAR _Result = DIVIDE(_Num, _Denom) RETURN _Result

Re: Plot average by legend category against time

oliverblane — Fri, 24 Dec 2021 14:10:38 GMT

Hi PaulOlding, thank you for your reply.

Unfortunately this does not seem to solve the problem - I see the logic behind the measure, but I do not understand the result on the plot:

Thanks again for your help.

Re: Plot average by legend category against time

v-yanjiang-msft — Wed, 29 Dec 2021 08:13:03 GMT

Hi oliverblane ,

According to your description, here's my solution.

Average is a measure.

Average = DIVIDE ( [Cumulative LRN], CALCULATE ( [Cumulative LRN], FILTER ( ALLSELECTED ( 'DateTable' ), MAX ( 'DateTable'[Date] ) ) ) )

Get the average value.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

Re: Plot average by legend category against time

oliverblane — Tue, 04 Jan 2022 09:31:03 GMT

Hi v-yanjiang-msft, apologies for the late reply.

Thank you very much for this. I have a further question - what if I wanted to divide by the number of unique establishments, for example each monthly value for Location A to be divided by 4 instead of 5?
Thanks!

Re: Plot average by legend category against time

v-yanjiang-msft — Tue, 04 Jan 2022 09:47:41 GMT

Hi oliverblane ,

According to your description, you can modify the Cumulative LRN formula like this:

Cumulative LRN = var MaxDate = MAX(DateTable[Date]) return SWITCH( TRUE(), SELECTEDVALUE(LegendCategories[Field]) = "Location", CALCULATE( DISTINCTCOUNT( 'SubTable'[Establishment] ), KEEPFILTERS( DateTable[Date] <= MaxDate ), ALLSELECTED( DateTable ), USERELATIONSHIP(LegendCategories[Category], 'SubTable'[Location Category]) ), SELECTEDVALUE(LegendCategories[Field]) = "Content Provider", CALCULATE( DISTINCTCOUNT( 'SubTable'[Establishment] ), KEEPFILTERS( DateTable[Date] <= MaxDate ), ALLSELECTED( DateTable ), USERELATIONSHIP(LegendCategories[Category], 'SubTable'[Content Provider]) ), SELECTEDVALUE(LegendCategories[Field]) = "Lot", CALCULATE( DISTINCTCOUNT( 'SubTable'[Establishment] ), KEEPFILTERS( DateTable[Date] <= MaxDate ), ALLSELECTED( DateTable ), USERELATIONSHIP(LegendCategories[Category], 'SubTable'[Lot] ) ))

I modify "DISTINCTCOUNT( 'SubTable'[LRN] )" with "DISTINCTCOUNT( 'SubTable'[Establishment] )"

Best Regards,
Community Support Team _ kalyj

Re: Plot average by legend category against time

oliverblane — Tue, 04 Jan 2022 10:14:48 GMT

Thanks again v-yanjiang-msft, this seems to work! I appreciate all your help.