Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate values associated to min index and sum by group

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 idweekproductsortspend
11Apples117
11Bananas320
11Oranges511
11Grapes218
11Pears414
12Apples78
12Bananas85

This customer has a spend of:

  • 17 when no filters applied
  • 18 when Apples are excluded
  • 8 when Week 1 is excluded

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:

productcustomersspend
Apples359
Bananas479
Oranges475
Grapes484
Pears355
Total482

Filter out Bananas:

productcustomersspend
Apples359
Oranges475
Grapes477
Pears355
Total479

Filter out Bananas & select Weeks 2 & 3:

productcustomersspend
Apples329
Oranges445
Grapes324
Pears226
Total456

 

Any help would be greatly appreciated,

Jon

 

Example data:

customer idweekproductsortspend
11Apples117
11Bananas320
11Oranges511
11Grapes218
11Pears414
12Apples78
12Bananas85
12Oranges610
13Grapes94
13Pears102
21Bananas119
21Oranges216
21Grapes314
22Apples412
22Bananas59
22Oranges64
23Apples73
31Oranges116
31Grapes215
31Pears312
32Oranges410
32Bananas56
32Grapes65
33Bananas72
41Apples130
41Bananas234
41Oranges332
41Grapes437
41Pears529
42Pears624
42Bananas722
42Oranges821
42Apples99
42Grapes1015
43Grapes1110
43Pears1218
43Bananas138
43Apples144
43Oranges153

 

 

2 ACCEPTED SOLUTIONS
v-frfei-msft
Community Support
Community Support

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

Capture.PNG

 

BTW, pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Anonymous
Not applicable

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

Capture.PNG

Thank you for your help!

Jon

View solution in original post

4 REPLIES 4
v-frfei-msft
Community Support
Community Support

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

Capture.PNG

 

BTW, pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

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

Capture.PNG

Thank you for your help!

Jon

Anonymous
Not applicable

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:

  • ID 1 - min(sort) = 6 for Oranges so 10 spend
  • ID 2 - min(sort) = 4 for Apples so 12 spend
  • ID 3 - min(sort) = 4 for Oranges so 10 spend
  • ID 4 - min(sort) = 6 for Pears so 24 spend

Thank you for your help so far though!

Jon

Anonymous
Not applicable

Hi @Anonymous 

 

have you tried an expression like this one?:

 

Measure = CALCULATE(SUM(Table[spend]);filter(Hoja1;Hoja1[sort] = MIN(Table[sort])))
 
Please, try it and comment if it works.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.