Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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
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.
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!
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
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:
Thanks again for your help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |