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
emveha
Resolver III
Resolver III

Calculate the number of combined items used in other orders

hi 

want to know how many times 1 item is sold in combination with another item.

So, on the side of data we have this:

country        year     ordernr     item

Germany     2019     123456     Sku12345

Germany     2019     123456     Sku23456

France         2019     298765     Sku34567

France         2018     298765     Sku34567

UK               2019     287654     Sku44556

UK               2019     287654     Sku45456

As a result we should have this as output:

country        year     ordernr     item             unique combination               nr of items used

Germany     2019     123456     Sku12345    Sku12345 & Sku23456                      2    

Germany     2019     123456     Sku23456    none as the result is already             0 

                                                                     displayed in the field above

France         2019     298765     Sku34567    Sku34567                                           1

France         2018     298765     Sku34567    Sku34567                                           1

UK               2019     287654     Sku44556    Sku44556 & Sku45456                      2 

UK               2019     287654     Sku45456    none as the result is already              0 

                                                                     displayed in the field above

To get this result you check first country then year then order nr and as a result you count the number of unique combinations.

Any ideas??

gracias

M

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

HI, @emveha 

You could use these two formula to create the measure

unique combination = 
IF (
    CALCULATE ( MIN ( 'Table'[item] ), ALL ( 'Table'[item] ) )
        = SELECTEDVALUE ( 'Table'[item] ),
    CALCULATE (
        CONCATENATEX ( VALUES ( 'Table'[item] ), [item], "&", MAX ( 'Table'[item] ) ),
        ALL ( 'Table'[item] )
    ),
    "none as the result is already displayed in the field above"
)
nr of items used = IF(CALCULATE(MIN('Table'[item]),ALL('Table'[item]))=SELECTEDVALUE('Table'[item]),CALCULATE(COUNTA('Table'[item]),ALL('Table'[item])),0)

Result:

1.JPG

and here is my sample pbix file, please try it.

 

Best Regards,

Lin

 

 

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

View solution in original post

1 REPLY 1
v-lili6-msft
Community Support
Community Support

HI, @emveha 

You could use these two formula to create the measure

unique combination = 
IF (
    CALCULATE ( MIN ( 'Table'[item] ), ALL ( 'Table'[item] ) )
        = SELECTEDVALUE ( 'Table'[item] ),
    CALCULATE (
        CONCATENATEX ( VALUES ( 'Table'[item] ), [item], "&", MAX ( 'Table'[item] ) ),
        ALL ( 'Table'[item] )
    ),
    "none as the result is already displayed in the field above"
)
nr of items used = IF(CALCULATE(MIN('Table'[item]),ALL('Table'[item]))=SELECTEDVALUE('Table'[item]),CALCULATE(COUNTA('Table'[item]),ALL('Table'[item])),0)

Result:

1.JPG

and here is my sample pbix file, please try it.

 

Best Regards,

Lin

 

 

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

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.