cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Get 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      Stack1Stack2Stack3Stack4
System1146 
System225  
System33 7 
System4891011
System512131415

 

and the table looks like this:

 

System      Stack      Benchmark
System1Stack11
System1Stack24
System1Stack36
System2Stack12
System2Stack25
System3Stack13
System3Stack37
System4Stack18
System4Stack29
System4Stack310
System4Stack411
System5Stack112
System5Stack213
System5Stack314
System5Stack415

 

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  
System114
System225
System33 
System489
System51213
Average5.757.75
 

When I want to compare Stack1, Stack2, and Stack3, following rows are used:

System      Stack1Stack2Stack3
System1146
System225 
System33 7
System48910
System5121314
Average78.710
 

Can you please help how to write the "Average"?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Get average on rows continas no blank

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

test_ignore_null_matrix.PNG

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.

View solution in original post

4 REPLIES 4
Highlighted
Responsive Resident
Responsive Resident

Re: Get average on rows continas no blank

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

 

 

 

 

 

 

Highlighted
Super User IV
Super User IV

Re: Get average on rows continas no blank

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Get average on rows continas no blank

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.

Highlighted
Microsoft
Microsoft

Re: Get average on rows continas no blank

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

test_ignore_null_matrix.PNG

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.

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors