cancel
Showing results for
Did you mean: Frequent Visitor

Count of a measure with no row level context

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

1 ACCEPTED SOLUTION  Super User

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 =
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

4 REPLIES 4  Super User

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 =
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 Frequent Visitor

@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.  Super User

@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()))

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User! Frequent Visitor

@amitchandak Thank you for commenting  