Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm still pretty new to PBI. I have Free PBI Desktop v2.100.1182 (64 bit free version Dec 2021) on Windows 10. I use an ODBC driver to connect to a Postgresql database server.
I have a matrix with columns for department, which can be drilled down to cost center. 4 values are summed by cost center but some cost centers have all 4 values of zero. I want to hide these cost centers where the 4 values are summed to zero. So I want to hide the row Dept 300 Cost center 325.
In the above example you see the bold Dept 300, with a cost center 325 below it. I would like to hide that row because the sum of the 4 value columns is zero. I have right-clicked CostCenter field and "Show items with no data" is unchecked. The Costctr of 325 is shown because it has a value, which is zero. All 4 value columns, esthours, acthours, estcost, actcost, are all calculated in the matrix for me. The matrix settings looks like this:
Anyone have any ideas? Thank you!
I've already done some searching on this forum with no luck. I found a few similar articles but they did not help. The "Show items with no data" only seems to apply to blanks and nulls, not zero values.
Are you using measures for the values?
Proud to be a Super User!
Paul on Linkedin.
No, the values are not measures, they are numeric fields.
Use measures instead, alon g the lines of:
Remove 0 =
SUMX (
'Table',
CALCULATE ( IF ( SUM ( 'Table'[Value] ) <> 0, SUM ( 'Table'[Value] ) ) )
)
Proud to be a Super User!
Paul on Linkedin.
Thank you. If my fields I am summing are called EstHours, ActHours, EstCost, ActCost, where in your formula are all 4 fields summed?
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |