Percentage of orders which contains 2 or more colours

[Grofu]

Hello,

Is there any possibility in DAX to create a column/measure to show me the % of orders that contains at least 2 colours ?

It seems that i cannot attach excels so this is what it should like:

Order   Product colour

AAA      White

BBB       Black

CCC       White

CCC      Black

Basically i have 3 different orders and one of them has (at least) 2 orders which means that it should return 33.33%.

I am able to do it with a visual to count distinct the colours per orders but i want a percentage as well (visual is not helping since i have too many lines to extract in excel).

Thanks

[v-lionel-msft · Accepted solution]

Hi @Grofu ,

Try the measures:

count_color = 
CALCULATE(
    DISTINCTCOUNT(Sheet10[Product colour]),
    ALLEXCEPT(
        Sheet10,
        Sheet10[Order]
    )
)

count_order = 
CALCULATE(
    DISTINCTCOUNT(Sheet10[Order]),
    FILTER(
        Sheet10,
        [count_color] >= 2
    )
)

Percentage = 
VAR x = 
CALCULATE(
    DISTINCTCOUNT(Sheet10[Order]),
    ALL(Sheet10)
)
RETURN
DIVIDE(
    [count_order],
    x,
    BLANK()
)

Best regards,
Lionel Chen

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

[Anonymous]

First create a calculated column that count the list of distinct colours

DistinctColours=
var thisOrder=YourTable[Order]
RETURN
DISTINCTCOUNT(FILTER(YourTable;YourTable[Order]=thisOrder);YourTable[ProductColor])

This should return

Order   Product colour DistinctColours

AAA      White              1

BBB       Black               1

CCC       White             2

CCC      Black               2

Unless you already have a "Orders" table (I suppose not, by looking at your example) now the next step is to summarize (group) your table to have a order table

Orders=SUMMARIZE(YourTable;YourTable[Order];YourTable[DistinctColours])

And this will generate

Order    DistinctColours

AAA             1

BBB               1

CCC             2

Then...I'll leave the next steps to you 🙂

Notice that I split this in several steps but it can be done in a single operation. This way is more explanatory

[Grofu]

Thanks for your tip!

However it is not working because Distinctount does not allow me to use 2 parameters (filter+ product colours).

DistinctColours=
var thisOrder=YourTable[Order]
RETURN
DISTINCTCOUNT(FILTER(YourTable;YourTable[Order]=thisOrder);YourTable[ProductColor])

Too many arguments were passed to the DISTINCTCOUNT function. The maximum argument count for the function is 1.

[Anonymous]

Yeah, I did write the DAX on the fly ehehe

var thisOrder=YourTable[Order]
RETURN
CALCULATE(DISTINCTCOUNT(YourTable[ProductColor]);
filter(YourTable;YourTable[Order]=thisOrder)
)