Get average on rows continas no blank

[kongyuancn]

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"?

[V-lianl-msft · Accepted 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]
                       ))

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[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))

[speedramps]

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

[kongyuancn]

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.