Forum Discussion
Helper IIGet average on rows continas no blank
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"?
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.
4 Replies
speedrampsSuper User
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
kongyuancnThnak 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.
- amitchandak
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)) 
V-lianl-msftCommunity Support
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.
