cancel
Showing results for
Search instead for
Did you mean:
Helper III

## Plot average by legend category against time

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/EdHNHYsYxGFOllt...

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

2 ACCEPTED SOLUTIONS
Community Support

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.

Community Support

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

6 REPLIES 6
Community Support

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.

Helper III

Hi @v-kalyj-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!

Community Support

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

Helper III

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

Super User

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``````
Helper III

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.

## Helpful resources

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors