Count of a measure with no row level context

[C4L84]

Hi all,

I'm struggling to find a solution to a seemingly straightforward requirement...

I have a simple matrix visual which contains data similar to this (sorry, I cannot post actual content):

Columns B and C are measures like this:

CALCULATE(SUM('Invoiced sales'[Sales]),FILTER('Calendar','Calendar'[Year] = [CY])) -- CY is a different measure because our financial runs from Feb to Jan.

Column D is simply column C - column B.

I need to use a card visual to give a count of customers where the difference is less than 0, so in this instance it would say 5.

Effectively the logic is something like sum(if [2021 - 2019 sales] < 0 then 1, 0) but I cannot get this to work!

TEST = sum(IF([CY vs CY -2] < 0, 1))

How do I make this count work? Would a calculated / virtual table be required? I hope not as I have no experience using these!

Thank you

[selimovd · Accepted solution]

Hey @C4L84 ,

the problem with your approach is that there is not filter context. The formula doesn't know that you want to measure by Customer.

Try the following approach:

Amount Customers with Negative Difference =
-- Replicates the table with Customers and the Measure [Difference]
VAR vBaseTable =
    ADDCOLUMNS(
        VALUES( myTable[Customers] ),
        "@Difference", [Difference]
    )
-- Filters that table for the negative rows
VAR vFilterNegative =
    FILTER(
        vBaseTable,
        [@Difference] < 0
    )
RETURN
-- returns the amount of negative rows
    SUMX(
        vFilterNegative,
        1
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[C4L84]

@selimovd - Yes this works!

This is what I meant by no row level context and why I thought that using a calculated / virtual table may be the way to make it work.

Thank you so much for taking the time to reply with a solution. I hope you receive some good karma.

[amitchandak]

@C4L84 , Assume you have measures like these with date table

This Year = CALCULATE(sum("order"[Qty]),filter(ALL("Date"),"Date"[Year]=max("Date"[Year])))
2nd  Last Year = CALCULATE(sum("order"[Qty]),filter(ALL("Date"),"Date"[Year]=max("Date"[Year])-2))

then

countx(values(customer[customer]), if([This Year] <[2nd  Last Year], [customer], blank()))

[C4L84]

@amitchandak Thank you for commenting