Calculate transactions that contain items in category A only, B only, C only, or a combo

[Waters29]

Hi all,

I'm working on a problem that seems like it should have a simple answer, but I have had some trouble finding a good way to write out the DAX formula.

I have a dataset consisting of sales and orders for categories A, B, and C. I need to flag each order as consisting of A only, B only, C only, or a combo of any of A/B/C. Is there an easy way to do this?

I've found a similar answer to what I'm looking for on one of these posts, so maybe I can still use this, but I need to be able to perform other calculations (such as total orders, total revenue, AOV, avg units per order, median order value, etc.) for each category. E.g. I need to be able to show total orders for A only, B only, C only, or combo:

I'm currently calculating a separate measure for category A total orders, separate measure for category B total orders, separate measure for category C total orders, etc. but I need to be able to create one measure/column to calculate total orders split out by category, otherwise I'm looking at 50+ measures as you can imagine. It would be much better if I can perform calculations for total orders, total revenue, AOV, etc. on one column.

Here's what I'm using as a calculated column to find orders that contain category A only (repeating the same for category B and category C), so a category A returns as 'A', otherwise returns as 'NO':

A Only =

VAR varOrder = 'OrderTable'[Order ID]

VAR varFilter = FILTER ( 'OrderTable', 'OrderTable'[Order ID] = varOrder )

RETURN

IF (

CALCULATE ( DISTINCTCOUNT( 'OrderTable'[Order ID] ), 'OrderTable'[Category] = "A", varFilter ) > 0,

"A",

"NO"

)

 Then using this measure to calculate orders for category A only:

A Only Orders =

CALCULATE(

DISTINCTCOUNT('FCT Transaction Data'[Transaction ID] ),

'OrderTable'[A Only] = "A", 'OrderTable'[B Only] = "NO", 'OrderTable'[C Only] = "NO"

)

And this measure to calculate for category B only (then separate measure for C only and separate measure for combo):

B Only Orders =

CALCULATE(

DISTINCTCOUNT('FCT Transaction Data'[Transaction ID] ),

'OrderTable'[B Only] = "B", 'OrderTable'[A Only] = "NO", 'OrderTable'[C Only] = "NO"

)

Here is a sample dataset: https://drive.google.com/file/d/1_3ZjQkiw0u8N88YjRbYHW6VFvCvBiizb/view?usp=sharing

I come from a team of Power BI users so we previously would lean on each other for help with situations such as these, but I unfortunately don't have that luxury anymore.

Thanks so much!

[AlexisOlson]

A sample dataset is almost always useful. Sharing an example .pbix is even better.

I think creating a calculated column like this on OrderTable might be useful:

OrderCategory =
VAR Categories =
    CALCULATETABLE (
        VALUES ( OrderTable[Category] ),
        ALLEXCEPT ( OrderTable, OrderTable[Order ID] )
    )
RETURN
    IF ( HASONEVALUE ( Categories ), Categories & " Only", "Combo" )

[Waters29]

Hi @AlexisOlson ,

Thank you for your quick reply. I tried the above formula but it labeled all orders as 'Combo'. Do you know how I can add a sample .pbix file? I don't see an attachment option - should I publish to PBI Service? I don't think you'd have the ability to see the backend tables if I did that.

Thank you!

[AlexisOlson]

Upload it to e.g. DropBox/Google Drive/OneDrive and share the link.

[Waters29]

@AlexisOlson Ah, understood - please let me know if you have trouble accessing this file: https://drive.google.com/file/d/1_3ZjQkiw0u8N88YjRbYHW6VFvCvBiizb/view?usp=sharing

[Waters29]

@AlexisOlson I was able to figure this out using SWITCH() true logic:

OrderCategory =

SWITCH(

TRUE(),

'OrderTable'[A Only] = "A" && 'OrderTable'[B Only] = "NO" && 'OrderTable'[C Only] = "NO", "A",

'OrderTable'[B Only] = "B" && 'OrderTable'[A Only] = "NO" && 'OrderTable'[C Only] = "NO", "B",

'OrderTable'[C Only] = "C" && 'OrderTable'[A Only] = "NO" && 'OrderTable'[B Only] = "NO", "C",

"Combo"

)

This gives me A for orders only containing A, B for orders containing only B, C for orders only containing C, and Combo for all else.

Really appreciate your assistance, thank you!

[AlexisOlson · Accepted solution]

Ah, I forgot HASONEVALUE doesn't work on variables.

This should work and be a bit cleaner than all the calculated columns and a SWITCH:

OrderCategory = 
VAR Categories =
    CALCULATETABLE (
        VALUES ( OrderTable[Category] ),
        ALLEXCEPT ( OrderTable, OrderTable[Order ID] )
    )
RETURN
    IF ( COUNTROWS ( Categories ) = 1, Categories, "Combo" )

[Waters29]

@AlexisOlson excellent - this is much cleaner and I really like not have to using those additional calculated columns.

Thank you much!