The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi guys,
Requesting your help for a DAX command. PFB the sample tables:
Category Dist Value
A | 0 | 200 |
B | 100 | 300 |
C | 0 | 400 |
D | 300 | 500 |
E | 0 | 100 |
F | 400 | 300 |
Category Dist Value
A | 0 | 200 |
B | 0 | 300 |
C | 0 | 400 |
D | 0 | 500 |
E | 0 | 100 |
F | 0 | 300 |
From the above two tables, I need to find the average of Dist - average of values where dist should not be zero. For example, from the first table the average of dist comes to be 266.66(neglecting zeros) and average of Value- 300, for the second table, the avg of dist-0 and average of value is 300, Can anyone help me with the dax formula. I used the below formula but it filtered some records.
Calculate((average(dist))- (average(value)), filter(table1,table1[dist]<>0)),
the above formula filters the values records also. appreciate if anyone can help me with this requirement.
Thank you!
HI @AV_04
Please try
=
CALCULATE ( AVERAGE ( 'Table'[dist] ), 'Table'[dist] <> 0 )
- AVERAGE ( 'Table'[Value] )
User | Count |
---|---|
25 | |
10 | |
8 | |
7 | |
6 |
User | Count |
---|---|
32 | |
12 | |
10 | |
10 | |
9 |