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.
Hi,
I am currently trying to use a card visual to display a measure that calculates the max of another measure as can be seen below, but i ran into a problem, the card can be filtered from the filter panel and since some of the data has spikes the spike is shown. Any suggestions on how I can filter out these spikes from the measure. Any help would be apriciated.
the card data has the max imbalance and and I have filtered the line chart using the filter panel but I can't do the same for the card.
Below is the DAX I used to show the maxium voltage imbalance from the V-imbal measure
MAX V-BMP01-IMBAL =
Var MaxVImbalBMP01 =
MAXX(
V_BMP,
'Voltage Measures'[V-BMP01-IMBAL]
)
RETURN
MaxVImbalBMP01
Below is the DAX for the line chart that shows V-imbalance measure
V-BMP01-IMBAL =
VAR ValueTableBMP01 =
UNION(
ROW("BMP01",AVERAGE(V_BMP[V-BMP01-AB])),
ROW("BMP01",AVERAGE(V_BMP[V-BMP01-BC])),
ROW("BMP01",AVERAGE(V_BMP[V-BMP01-CA]))
)
VAR BMP01ROWMAX =
MAXX(
ValueTableBMP01,[BMP01]
)
VAR BMP01ROWAVE =
AVERAGEX(
ValueTableBMP01,
[BMP01]
)
VAR BMP01IMBAL =
(BMP01ROWMAX - BMP01ROWAVE) / BMP01ROWAVE
Var MaxVImbalBMP01 =
MAXX(
V_BMP,
BMP01IMBAL
)
RETURN
MaxVImbalBMP01
Hi @equetzal
There's a couple things you can do. Calculate as variables either: the average and standard deviation of the data OR a high and low percentile (maybe 10th and 90th?).
When you're calculating the average you can filter for values between (average +/- 2 standard deviations) or (high and low percentiles). Either way will discard outliers.
Hope this helps!
Hi @littlemojopuppy
Thanks for the suggestion I have tried but the card comeout blank could you be so kind as to give an example.
Hi @equetzal Here's a sample from something similar I've done...
VAR PercentileCutoff =
PERCENTILEX.INC ( CalculateDaysOpen, [DaysOpen], .99 )
RETURN
AVERAGEX (
FILTER ( CalculateDaysOpen, [DaysOpen] >= 0 && [DaysOpen] <= PercentileCutoff ),
[DaysOpen]
)
Can you share your formula?
Surprised you're not getting an error. The PERCENTILEX.INC function requires a value between 0 and 1 for the third parameter. You entered 1.05.
If you want to exclude the outliers from the max calculation, calculate the percentile first, and then filter the table in the MAXX calculation. Use the code I provided as guidance for the order the steps should be in.
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |