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

Plot average by legend category against time

I have a table that looks like the following:

oliverblane_0-1640276674776.png

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:

oliverblane_2-1640280923393.png

 

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
v-kalyj-msft
Community Support
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.

vkalyjmsft_0-1640765495789.png

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.

 

View solution in original post

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

View solution in original post

6 REPLIES 6
v-kalyj-msft
Community Support
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.

vkalyjmsft_0-1640765495789.png

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.

 

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!

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

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

PaulOlding
Super User
Super User

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

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:

oliverblane_0-1640355008784.png

Thanks again for your help.

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!