Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello everyone,
I have this page for variance analysis. For selected variable_id (drill-through) we are able to see every scenarios and quote prices and their variances. I have created a table to filter the table view as I called Threshold. And using this measure below:
filter_param_threshold_test =
VAR _param = MAXX(ALLSELECTED(param_threshold[Value]),[Value])
RETURN
IF( _param > [variable var %], 1, 0)
and filter the table = 1, so when I filter to 66.05% like below
it filters out the third scenario. This works well. However since I use this measure to the table view and I can't use to filter individually for card visuals. They are not changing to the number that I would like to see it.
*I have shown the expected number with arrow signs.
How should I process to achieve what I want here?
Solved! Go to Solution.
Hi @brickanalyst , you can try to create separate measure:
Selected Threshold =
MAXX(ALLSELECTED(param_threshold[Value]), param_threshold[Value])
Avg Variable % Var (Filtered) =
VAR Threshold = [Selected Threshold]
VAR FilteredTable =
FILTER(
ALL('YourTableName'),
[variance_%_A_B] <= Threshold
)
RETURN
AVERAGEX(FilteredTable, [variance_%_A_B])
# of Scenarios (Filtered) =
VAR Threshold = [Selected Threshold]
RETURN
COUNTROWS(
FILTER(
ALL('YourTableName'),
[variance_%_A_B] <= Threshold
)
)
# of Scenarios Over 10% (Filtered) =
VAR Threshold = [Selected Threshold]
RETURN
COUNTROWS(
FILTER(
ALL('YourTableName'),
[variance_%_A_B] > 0.1 && [variance_%_A_B] <= Threshold
)
)
# of Scenarios Over 50% (Filtered) =
VAR Threshold = [Selected Threshold]
RETURN
COUNTROWS(
FILTER(
ALL('YourTableName'),
[variance_%_A_B] > 0.5 && [variance_%_A_B] <= Threshold
)
)
Replace the existing measures in your card visuals with the newly created measures to ensure they dynamically respect the slicer threshold.
Thank you, but I haven't got expected results on my end. This worked for me:
Avg Variable % Var (Filtered) =
VAR _param = MAXX(ALLSELECTED(param_threshold[Value]), [Value])
VAR _avgVar =
AVERAGEX(
VALUES('Table'[Assumption_UID]),
VAR _currentVar = [% variance abs]
RETURN IF(_currentVar <= _param, _currentVar, BLANK())
)
RETURN _avgVar
# of Scenarios (Filtered) =
VAR _param = MAXX(ALLSELECTED(param_threshold[Value]),[Value])
VAR SummaryTable =
SUMMARIZE(
'Table',
'Table'[Assumption_UID],
"VarianceAbs", [% variance abs]
)
RETURN
CALCULATE(
DISTINCTCOUNT('Table'[Assumption_UID]),
FILTER(
SummaryTable,
[VarianceAbs] <= _param
)
)
# of estimates over %10 variance =
VAR _param = MAXX(ALLSELECTED(param_threshold[Value]),[Value])
VAR SummaryTable =
SUMMARIZE(
'Table',
'Table'[Assumption_UID],
"VarianceAbs", [% variance abs]
)
VAR AssumptionTable =
ADDCOLUMNS(
SummaryTable,
"WithinThreshold", IF([VarianceAbs] <= _param, [VarianceAbs], BLANK())
)
VAR CountAboveThreshold =
COUNTROWS(
FILTER(
AssumptionTable,
[WithinThreshold] > 0.10
)
)
RETURN
IF(CountAboveThreshold <> BLANK(), CountAboveThreshold, 0)
I will accept this as a solution for this ticket, your measure may work for some other cases tho.
Thank you, but I haven't got expected results on my end. This worked for me:
Avg Variable % Var (Filtered) =
VAR _param = MAXX(ALLSELECTED(param_threshold[Value]), [Value])
VAR _avgVar =
AVERAGEX(
VALUES('Table'[Assumption_UID]),
VAR _currentVar = [% variance abs]
RETURN IF(_currentVar <= _param, _currentVar, BLANK())
)
RETURN _avgVar
# of Scenarios (Filtered) =
VAR _param = MAXX(ALLSELECTED(param_threshold[Value]),[Value])
VAR SummaryTable =
SUMMARIZE(
'Table',
'Table'[Assumption_UID],
"VarianceAbs", [% variance abs]
)
RETURN
CALCULATE(
DISTINCTCOUNT('Table'[Assumption_UID]),
FILTER(
SummaryTable,
[VarianceAbs] <= _param
)
)
# of estimates over %10 variance =
VAR _param = MAXX(ALLSELECTED(param_threshold[Value]),[Value])
VAR SummaryTable =
SUMMARIZE(
'Table',
'Table'[Assumption_UID],
"VarianceAbs", [% variance abs]
)
VAR AssumptionTable =
ADDCOLUMNS(
SummaryTable,
"WithinThreshold", IF([VarianceAbs] <= _param, [VarianceAbs], BLANK())
)
VAR CountAboveThreshold =
COUNTROWS(
FILTER(
AssumptionTable,
[WithinThreshold] > 0.10
)
)
RETURN
IF(CountAboveThreshold <> BLANK(), CountAboveThreshold, 0)
I will accept this as a solution for this ticket, your measure may work for some other cases tho.
Hi @brickanalyst , you can try to create separate measure:
Selected Threshold =
MAXX(ALLSELECTED(param_threshold[Value]), param_threshold[Value])
Avg Variable % Var (Filtered) =
VAR Threshold = [Selected Threshold]
VAR FilteredTable =
FILTER(
ALL('YourTableName'),
[variance_%_A_B] <= Threshold
)
RETURN
AVERAGEX(FilteredTable, [variance_%_A_B])
# of Scenarios (Filtered) =
VAR Threshold = [Selected Threshold]
RETURN
COUNTROWS(
FILTER(
ALL('YourTableName'),
[variance_%_A_B] <= Threshold
)
)
# of Scenarios Over 10% (Filtered) =
VAR Threshold = [Selected Threshold]
RETURN
COUNTROWS(
FILTER(
ALL('YourTableName'),
[variance_%_A_B] > 0.1 && [variance_%_A_B] <= Threshold
)
)
# of Scenarios Over 50% (Filtered) =
VAR Threshold = [Selected Threshold]
RETURN
COUNTROWS(
FILTER(
ALL('YourTableName'),
[variance_%_A_B] > 0.5 && [variance_%_A_B] <= Threshold
)
)
Replace the existing measures in your card visuals with the newly created measures to ensure they dynamically respect the slicer threshold.
Add the same measure as a visual filter to the other visuals.
@lbendlin
A card visual has a measure, it's not available to use another measure to filter.
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
9 | |
6 |