Plot average by legend category against time

[oliverblane]

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!

[v-yanjiang-msft · Accepted solution]

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.

[v-yanjiang-msft · Accepted solution]

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.

[oliverblane]

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!

[v-yanjiang-msft · Accepted solution]

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

[oliverblane]

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

[PaulOlding]

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

[oliverblane]

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.