Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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!
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
Solved! Go to 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
)
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
)
@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.
@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()))
@amitchandak Thank you for commenting
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |