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 All,
I'm trying to get compare benchmark on different software stack. Here is a simple data set:
System | Stack1 | Stack2 | Stack3 | Stack4 |
System1 | 1 | 4 | 6 | |
System2 | 2 | 5 | ||
System3 | 3 | 7 | ||
System4 | 8 | 9 | 10 | 11 |
System5 | 12 | 13 | 14 | 15 |
and the table looks like this:
System | Stack | Benchmark |
System1 | Stack1 | 1 |
System1 | Stack2 | 4 |
System1 | Stack3 | 6 |
System2 | Stack1 | 2 |
System2 | Stack2 | 5 |
System3 | Stack1 | 3 |
System3 | Stack3 | 7 |
System4 | Stack1 | 8 |
System4 | Stack2 | 9 |
System4 | Stack3 | 10 |
System4 | Stack4 | 11 |
System5 | Stack1 | 12 |
System5 | Stack2 | 13 |
System5 | Stack3 | 14 |
System5 | Stack4 | 15 |
Some system run only on some stacks. So when I want to get averages of the stacks, I don't want to include the systems those didn't ran benchmark.
For example, when I compare stack1 and stack2, I only want following (bolded rows):
System | Stack1 | Stack2 |
System1 | 1 | 4 |
System2 | 2 | 5 |
System3 | 3 | |
System4 | 8 | 9 |
System5 | 12 | 13 |
Average | 5.75 | 7.75 |
When I want to compare Stack1, Stack2, and Stack3, following rows are used:
System | Stack1 | Stack2 | Stack3 |
System1 | 1 | 4 | 6 |
System2 | 2 | 5 | |
System3 | 3 | 7 | |
System4 | 8 | 9 | 10 |
System5 | 12 | 13 | 14 |
Average | 7 | 8.7 | 10 |
Can you please help how to write the "Average"?
Solved! Go to Solution.
Hi @kongyuancn ,
Try to create a measure like this and put it into visual level filter.
IgnoreNullValues = IF (
CALCULATE(DISTINCTCOUNT('Table'[Stack]),ALLSELECTED('Table'[Stack]))
= COUNTROWS(ALLSELECTED('Table'[Stack])),
MAX('Table'[Benchmark]
))
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @kongyuancn ,
Try to create a measure like this and put it into visual level filter.
IgnoreNullValues = IF (
CALCULATE(DISTINCTCOUNT('Table'[Stack]),ALLSELECTED('Table'[Stack]))
= COUNTROWS(ALLSELECTED('Table'[Stack])),
MAX('Table'[Benchmark]
))
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@kongyuancn , try like
measure=
var _sel = countx(allselected(Table),table[Stack])
var _allex =countx(allexpect(Table,table[Stack]),table[Stack])
return
calculate(average(Table[Benchmark]), filter(table,_allex=_sel))
Hi Kong
Please consider this solution
Create your Benchmark table
System | Stack1 | Stack2 | Stack3 | Stack4 |
System1 | 1 | 4 | 6 | |
System2 | 2 | 5 | ||
System3 | 3 | 7 | ||
System4 | 8 | 8 | 10 | 11 |
System5 | 12 | 13 | 14 |
Right click the System column and Unpivot other columns
Rename Attribute to Stack#
Create DAX measures:-
Total score = SUM(Benchmarks[Value]) -- total score
NO stacks = COUNTROWS(Benchmarks) -- number of stacks
Ave benchmark = DIVIDE([Total score],[NO stacks],BLANK()) -- average
Create a table report with:-
System
Total score
NO stacks
Ave benchmark
Thnak you @speedramps for your quick response. But I think your solution is incorrect. I want to calculate the station based on the systems those have benchmarks on ALL of selected stacks.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |