Forum Discussion
Power bi - Average
Hi - I have the following data in the "First table" that Distinct-counts items. The data is based on items shown in the "Second table". As you can see in the "Second table" there are itemes with $0 value that is distorting my information. So, if I want to obtain average $ per item for each month in the second table, it would NOT be correct because items with $0 will influence (decrease) the average $ per item. I also cannot simply apply a filter to remove items with grand total = $0, because some of the items (highlighted in the second table) impact different months (example: positive in Jul and negative in Sep). Is there any way I can remove/not count items with $0 the month the belong? I am using Matrix tables from the visual pane. Thank you in advance.
Hi Anonymous ,
Please try below steps:
1. below is my test table
Table:
2. create a measure with below dax formula
Sumifs = VAR cur_month = SELECTEDVALUE ( 'Table'[Month] ) VAR cur_wo = SELECTEDVALUE ( 'Table'[WO#2] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Month] = cur_month && 'Table'[WO#2] = cur_wo ) RETURN SUMX ( tmp, [Total2] )3. add a table visual with fields and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous ,
Please try below steps:
1. below is my test table
Table:
2. create a measure with below dax formula
Sumifs = VAR cur_month = SELECTEDVALUE ( 'Table'[Month] ) VAR cur_wo = SELECTEDVALUE ( 'Table'[WO#2] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Month] = cur_month && 'Table'[WO#2] = cur_wo ) RETURN SUMX ( tmp, [Total2] )3. add a table visual with fields and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Anonymous Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
