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.
I have a list of customers, and their sales. What i am trying to do, is compare sales compared to other customers in the same region.
What i can't work out, is how to refer to the region of the customer currently selected in the slicer.
Customer A is in the slicer, so its showing all the sales info for customer A. They are in region 1.
How do i write a measure to compare Customer A sales to the rest of region 1?
I can do = CALCULATE(Sales, FILTER(Customer List, Customer List[Region])="region 1") to basically get what i want, but i need it to be dynamic so that when i change my slicer, it will know the region of the new customer. somethink like a me. operator?
Suggestions?
Solved! Go to Solution.
The structure of a measure that should do the trick is:
= CALCULATE ( Sales, ALL ( 'Customer List' ), VALUES ( 'Customer List'[Region] ) )
VALUES ( 'Customer List'[Region] ) returns the Regions of the current selection in the 'Customer List' table.
ALL ( 'Customer List' ) clears any other filters on this table (such as the selected Customer) so you are left with the sales for the entire Region.
Oh, I'm assuming that 'Customer List' is a separate dimension table, related to your main Sales table. If not, we would need to be more careful about which filters we clear with ALL.
Regards,
Owen
The structure of a measure that should do the trick is:
= CALCULATE ( Sales, ALL ( 'Customer List' ), VALUES ( 'Customer List'[Region] ) )
VALUES ( 'Customer List'[Region] ) returns the Regions of the current selection in the 'Customer List' table.
ALL ( 'Customer List' ) clears any other filters on this table (such as the selected Customer) so you are left with the sales for the entire Region.
Oh, I'm assuming that 'Customer List' is a separate dimension table, related to your main Sales table. If not, we would need to be more careful about which filters we clear with ALL.
Regards,
Owen
Perfect, thanks! it did exactly what i wanted
One thing im not sure i understand, doesn't values just generate a unique list? i.e Values('Customer List'[Region]) would create a table for the unique region names?
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |