Flag customers that exceed limits (and Sum by month using calculation)

[tinkertrust]

I'm sorry this is such a simple question, but I can't figure it out from googling. Can someone help me with following?

I have a table with daily actions against a table with contract maximums per month. I'm trying to create a table that lists customers that have exceeded their allowable question limit per month. In summary:

Where I have:

Questions submitted (daily) in Daily_Feed

Allowable questions per month in Client_Lookup

Calendar_Lookup table

I need to do something like:

1. Sum daily questions per client per month.

2. Flag where the sum of questions per month exceeds the allowable questions per month in the Client_Lookup.

 I can obviously do step 1 visually using a table, but that won't allow me to do step 2.

Your help would be hugely appreciated!!

[v-yulgu-msft · Accepted solution]

Hi @tinkertrust,

Please refer to below measures:

daily questions per client per month =
CALCULATE (
    COUNT ( Daily_Feed[Question] ),
    ALLEXCEPT ( Daily_Feed, Daily_Feed[Client], Daily_Feed[Date].[MonthNo] )
)

allowable questions per month =
CALCULATE (
    SUM ( Client_Lookup[Allowable questions] ),
    FILTER (
        Client_Lookup,
        Client_Lookup[Client] = SELECTEDVALUE ( Daily_Feed[Client] )
            && Client_Lookup[MonthNo] = SELECTEDVALUE ( Daily_Feed[Date].[MonthNo] )
    )
)


flag =
IF (
    Daily_Feed[daily questions per client per month]
        > [allowable questions per month],
    "exceed",
    BLANK ()
)

Best regards,

Yuliana Gu

[Seward12533]

Trick is you need to use an aggregate measure for the target even if it’s a single value.

NumQAksed = COUNTROWS(daily_feed)
CountLimit =SUM(client_limit[limit])
Flag=[NumQAsked]>[CountLimit]

Then use these measures in your table/matrix or other visual.

If you want to get fancy this will display an actual flag.

DisplayFlag = IF([Flag],UNICHAR(128681))

If you want show a flag if an aggregate measure exceeds limit when summarized at a higher level even if aggregate value looks ok. Search for bubble measure