Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I need to count the number of people charged for a room each night. The problem lies in when a charge is put in and then reversed because it was done in error. (People expected to stay but then don't or just input error)
See screenshot - This person would only have 1 on 11-22 and 1 on 11-23. I don't want the 11-24 and 11-25 to be counted. I tried a Count if ChgAmt > 0 but it doesn't zero out total. It does it per line. I tried removing duplicates but i would need both lines removed.
Any thoughts?
Solved! Go to Solution.
Hi @kattlees,
Please create a measure:
Sum amount = CALCULATE ( SUM ( RCC[ChgAmount] ), ALLEXCEPT ( RCC, RCC[RCC], RCC[ChgServDate] ) )
Add above measure into visual level filter and set it to be greater than 0.
Best regards,
Yuliana Gu
Hi @kattlees,
Please create a measure:
Sum amount = CALCULATE ( SUM ( RCC[ChgAmount] ), ALLEXCEPT ( RCC, RCC[RCC], RCC[ChgServDate] ) )
Add above measure into visual level filter and set it to be greater than 0.
Best regards,
Yuliana Gu
Assuming a new visual with Date value on rows,
TotalDailyCharge = SUM ( Table[DailyCharge] )
Then
Count people = COUNTROWS (FILTER ( VALUES ( Table[AcctNum] ), [TotalDailyCharge] <> 0 ) )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |