cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
C4L84
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):

 

C4L84_0-1628782269846.png

 

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

C4L84_1-1628783256545.png

 

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
selimovd
Super User
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 =
    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
 

View solution in original post

4 REPLIES 4
selimovd
Super User
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 =
    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
 

View solution in original post

C4L84
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.

 

amitchandak
Super User
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()))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

@amitchandak Thank you for commenting

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors