Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I know there are severals post with similar doubts but no one is suitable for my problem.
I have a Matrix-table like below wich is showing the average in the subtotals rows, what I would like to know what is the DAX formula to exclude the "0,00s" values from this subtotal-average, but not with a filter excluding the "number 5" because today number 5 doesnt have values but it will have them in a close future.
Are there any way to do that? There are similar problems on the forum but not the same.
Any help will be really appreciate it.
Thank you in advance.
Kind regards
ICR
Solved! Go to Solution.
Hi @ICRdatalover ,
Please download a demo from the attachment.
1. If you don't want to transform your data, you can create 7 measures like below.
Value1Measure = CALCULATE ( AVERAGE ( 'Sample'[value1] ), 'Sample'[value1] <> 0 )
2. I would suggest you transform the data structure.
Measure = CALCULATE ( AVERAGE ( Table1[Value] ), Table1[Value] <> 0 )
Best Regards,
Hi @PattemManohar!
Thank you for taking the time to help me.
The problem is that its and AVERAGE, when you do the SUM like you solution it works fine, because its a "0", but when you do it with average the total do the average with the "0" values, what Im struggling is trying to exclude this "0" from the average but showing it , not excluding it on a filter.
Im not sure if im explaining it properly, here is a pic of what happend with the average
Thank you for your help!
ICR
@ICRdatalover Ok Gotcha !!
Please try this as a New Measure.
Test236 = VAR _CountRows = COUNTROWS(FILTER(Test236MatrixZeroExclusion,Test236MatrixZeroExclusion[Value]>0)) VAR _Sum = SUM(Test236MatrixZeroExclusion[Value]) VAR _TotalAvg = _Sum/_CountRows RETURN IF(_Sum=0,0,_TotalAvg)
Proud to be a PBI Community Champion
Hi @PattemManohar!!
Now It looks exacly what I need, thanks a lot.
But how should I fit this measure in the table? My table just have "Rank" on rows and "Value1", "Value2", etc on values.
Something is wrong and there is an error like the image
Could you please share the .pbix ?Maybe I will understand it better. Thanks a lot Pattem!
Kind regards.
ICR
@ICRdatalover I can't see that you have mentioned any Columns field in the screenshot...
Proud to be a PBI Community Champion
Hi @PattemManohar!
Yes, probably thas the issue why your solutions doesnt work for me, I didnt add any columns, just Rows and Values in the Matrix Table.
How should I change the measure to fit it in this table?
Thank you in advance.
Kind regards
ICR.
@ICRdatalover Is it your table structure is aligned with the structure of sample input data that I've posted above ? If not, could you please post the sample data
Proud to be a PBI Community Champion
Yes sure!, here is the sample.
https://drive.google.com/open?id=1YaQvsWoE3HI4PjStnRu8V1AOMhs96gn_
I think my table was diferent than yours.
Kind regards
Hi @ICRdatalover ,
Please download a demo from the attachment.
1. If you don't want to transform your data, you can create 7 measures like below.
Value1Measure = CALCULATE ( AVERAGE ( 'Sample'[value1] ), 'Sample'[value1] <> 0 )
2. I would suggest you transform the data structure.
Measure = CALCULATE ( AVERAGE ( Table1[Value] ), Table1[Value] <> 0 )
Best Regards,
Hello!
That is exacly what I needed, problem solved! Also I will change the structure as well to use the second way.
Thanks a lot for taking the time to help me @v-jiascu-msft and @PattemManohar!!
Kind regards.
ICR:
@ICRdatalover Please try as below
Proud to be a PBI Community Champion
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |