Forum Discussion

kattlees's avatar
kattlees
Post Patron
8 years ago
Solved

How to remove records during count

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?

 

Need Help
Tutorial Requests
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

2 Replies

  • mattbrice's avatar
    mattbrice
    Solution Sage
    8 years ago

    Assuming a new visual with Date value on rows, 

     

    TotalDailyCharge = SUM ( Table[DailyCharge] )

     Then

     

    Count people = COUNTROWS (FILTER ( VALUES ( Table[AcctNum] ), [TotalDailyCharge] <> 0 ) )
  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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