March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi Guys, I have this measure:
CalcuateData =
var calcType = SELECTEDVALUE('Calculate By'[ComputeBy])
RETURN
SWITCH(
TRUE(),
calcType = "Average", AVERAGEX(VALUES(car[ExtractedDate].[Date]), CALCULATE(SUM(car[Cnt]))),
MEDIANX(VALUES(car[ExtractedDate].[Date]),CALCULATE(SUM(car[Cnt])))
)
For the average, I got the result:
but for the median:
I only have two months. It seems like the month should be finished before having the value for median, is there a way to have the median regardless if the month is not finished?
Please see sample raw data:
Team | Type | ExtractedDate | Cnt |
Team Red | Sedan | 02/09/2021 | 3 |
Team Red | Sedan | 03/09/2021 | 3 |
Team Red | Sedan | 09/09/2021 | 3 |
Team Red | Sedan | 27/08/2021 | 4 |
Team Red | Hatchback | 08/09/2021 | 4 |
Team Red | Hatchback | 09/09/2021 | 5 |
Team Red | Hatchback | 05/11/2021 | 1 |
Team Blue | Hatchback | 27/08/2021 | 1 |
Team Blue | Hatchback | 30/08/2021 | 1 |
Team Blue | Hatchback | 31/08/2021 | 2 |
Team Blue | Sedan | 17/11/2021 | 4 |
Team Blue | Sedan | 18/11/2021 | 4 |
Team Blue | Sedan | 19/11/2021 | 4 |
Team Blue | Hatchback | 27/10/2021 | 1 |
Team Yellow | Hatchback | 09/09/2021 | 6 |
Team Yellow | Hatchback | 10/09/2021 | 5 |
Team Yellow | Hatchback | 13/09/2021 | 4 |
Team Yellow | Sedan | 14/09/2021 | 3 |
Any tips? Thank you in advance
Solved! Go to Solution.
@Anonymous , Try with minor changes
CalcuateData =
var calcType = Max('Calculate By'[ComputeBy])
RETURN
SWITCH(
TRUE(),
calcType = "Average", AVERAGEX(VALUES(car[ExtractedDate]), CALCULATE(SUM(car[Cnt]))),
MEDIANX(VALUES(car[ExtractedDate]),CALCULATE(SUM(car[Cnt])))
)
@Anonymous , Try with minor changes
CalcuateData =
var calcType = Max('Calculate By'[ComputeBy])
RETURN
SWITCH(
TRUE(),
calcType = "Average", AVERAGEX(VALUES(car[ExtractedDate]), CALCULATE(SUM(car[Cnt]))),
MEDIANX(VALUES(car[ExtractedDate]),CALCULATE(SUM(car[Cnt])))
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
125 | |
85 | |
69 | |
54 | |
45 |
User | Count |
---|---|
204 | |
106 | |
98 | |
65 | |
54 |