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,
I'm trying to calculate the values which are associated to the minimum sort value per customer, with a sum by product and the overall total showing the sum of values associated to the minimum sort value by customer for any product.
So essentially sum if the sort = MIN(sort) for each customer
For Example:
customer id | week | product | sort | spend |
1 | 1 | Apples | 1 | 17 |
1 | 1 | Bananas | 3 | 20 |
1 | 1 | Oranges | 5 | 11 |
1 | 1 | Grapes | 2 | 18 |
1 | 1 | Pears | 4 | 14 |
1 | 2 | Apples | 7 | 8 |
1 | 2 | Bananas | 8 | 5 |
This customer has a spend of:
I figured out a way for when the data is static (just sum where sort = 1) but the requirement is for the result to be dynamic based on the chosen products/weeks which is where I am getting stuck as 1 is not necessarily the minimum sort value when filtered, and the value to be summed is not always the customer's maximum spend.
I've provided some made up sample data below, but essentially the overall output would ideally look like:
No FIlters:
product | customers | spend |
Apples | 3 | 59 |
Bananas | 4 | 79 |
Oranges | 4 | 75 |
Grapes | 4 | 84 |
Pears | 3 | 55 |
Total | 4 | 82 |
Filter out Bananas:
product | customers | spend |
Apples | 3 | 59 |
Oranges | 4 | 75 |
Grapes | 4 | 77 |
Pears | 3 | 55 |
Total | 4 | 79 |
Filter out Bananas & select Weeks 2 & 3:
product | customers | spend |
Apples | 3 | 29 |
Oranges | 4 | 45 |
Grapes | 3 | 24 |
Pears | 2 | 26 |
Total | 4 | 56 |
Any help would be greatly appreciated,
Jon
Example data:
customer id | week | product | sort | spend |
1 | 1 | Apples | 1 | 17 |
1 | 1 | Bananas | 3 | 20 |
1 | 1 | Oranges | 5 | 11 |
1 | 1 | Grapes | 2 | 18 |
1 | 1 | Pears | 4 | 14 |
1 | 2 | Apples | 7 | 8 |
1 | 2 | Bananas | 8 | 5 |
1 | 2 | Oranges | 6 | 10 |
1 | 3 | Grapes | 9 | 4 |
1 | 3 | Pears | 10 | 2 |
2 | 1 | Bananas | 1 | 19 |
2 | 1 | Oranges | 2 | 16 |
2 | 1 | Grapes | 3 | 14 |
2 | 2 | Apples | 4 | 12 |
2 | 2 | Bananas | 5 | 9 |
2 | 2 | Oranges | 6 | 4 |
2 | 3 | Apples | 7 | 3 |
3 | 1 | Oranges | 1 | 16 |
3 | 1 | Grapes | 2 | 15 |
3 | 1 | Pears | 3 | 12 |
3 | 2 | Oranges | 4 | 10 |
3 | 2 | Bananas | 5 | 6 |
3 | 2 | Grapes | 6 | 5 |
3 | 3 | Bananas | 7 | 2 |
4 | 1 | Apples | 1 | 30 |
4 | 1 | Bananas | 2 | 34 |
4 | 1 | Oranges | 3 | 32 |
4 | 1 | Grapes | 4 | 37 |
4 | 1 | Pears | 5 | 29 |
4 | 2 | Pears | 6 | 24 |
4 | 2 | Bananas | 7 | 22 |
4 | 2 | Oranges | 8 | 21 |
4 | 2 | Apples | 9 | 9 |
4 | 2 | Grapes | 10 | 15 |
4 | 3 | Grapes | 11 | 10 |
4 | 3 | Pears | 12 | 18 |
4 | 3 | Bananas | 13 | 8 |
4 | 3 | Apples | 14 | 4 |
4 | 3 | Oranges | 15 | 3 |
Solved! Go to Solution.
Hi @Anonymous ,
To create two measures as below.
Measure = VAR k = ALLSELECTED ( 'Table' ) VAR a = CALCULATE ( MIN ( 'Table'[sort] ), ALLEXCEPT ( 'Table', 'Table'[product], 'Table'[customer id] ), KEEPFILTERS ( k ) ) RETURN IF ( MAX ( 'Table'[sort] ) = a, SUM ( 'Table'[spend] ), BLANK () )
Measure 2 = SUMX('Table',[Measure])
BTW, pbix as attached.
Hi @v-frfei-msft ,
I've solved my problem by slightly adjusting your first measure and excluding product from the ALLEXCEPT filter:
Measure = VAR k = ALLSELECTED ( 'Table' ) VAR a = CALCULATE ( MIN ( 'Table'[sort] ), ALLEXCEPT ( 'Table','Table'[product],'Table'[customer id] ), KEEPFILTERS ( k ) ) RETURN IF ( MAX ( 'Table'[sort] ) = a, SUM ( 'Table'[spend] ), BLANK () )
Thank you for your help!
Jon
Hi @Anonymous ,
To create two measures as below.
Measure = VAR k = ALLSELECTED ( 'Table' ) VAR a = CALCULATE ( MIN ( 'Table'[sort] ), ALLEXCEPT ( 'Table', 'Table'[product], 'Table'[customer id] ), KEEPFILTERS ( k ) ) RETURN IF ( MAX ( 'Table'[sort] ) = a, SUM ( 'Table'[spend] ), BLANK () )
Measure 2 = SUMX('Table',[Measure])
BTW, pbix as attached.
Hi @v-frfei-msft ,
I've solved my problem by slightly adjusting your first measure and excluding product from the ALLEXCEPT filter:
Measure = VAR k = ALLSELECTED ( 'Table' ) VAR a = CALCULATE ( MIN ( 'Table'[sort] ), ALLEXCEPT ( 'Table','Table'[product],'Table'[customer id] ), KEEPFILTERS ( k ) ) RETURN IF ( MAX ( 'Table'[sort] ) = a, SUM ( 'Table'[spend] ), BLANK () )
Thank you for your help!
Jon
Hi @v-frfei-msft ,
Those two measures work for the details in the table so thank you!
However, do you know of a way of showing the total for Measure 2 as the sum of the valuse associated to the minimu sort?
I have a feeling it will have something to do with HASONEVALUE or ISINSCOPE but I'm just unsure as to the exact measure needed.
For example, with the selctions you have in the image (Weeks 2 & 3, no Bananas), the total should be 56:
Thank you for your help so far though!
Jon
Hi @Anonymous
have you tried an expression like this one?:
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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |