Power BI / DAX adding columns with Countax and values

0
votes

I have this query in Power BI, where each product can show many times. Each product can have one or two colors. Each color have a specific percentage.

enter image description here

In the Data view I want to add a couple of columns to show:

  1. *# colors- shows 1 or 2 depending of the number of colors
  2. New %- shows the blue % if there are two colors or the actual % if there is only one color, whatever it is

This is the desired final table. How do I get it in Power BI?

enter image description here

1
daxpowerbi-desktop

1 Answers

0
votes

So you need to create two calculated columns. We assume that the table name is T

the first one is the number of colors. We must filter by Product and count the number of distinct colors

# colors = CALCULATE( DISTINCTCOUNT( T[Color] ), ALLEXCEPT(T, T[Product]) )

The second one is more complex, since first we look for the Blue color percentage and if it is not found we get the MIN percentage, that in case of just one color is assured to be the desired one.

New % =
VAR BluePercent =
    CALCULATE (
        SELECTEDVALUE ( T[%] ),
        ALLEXCEPT ( T, T[Product] ),
        T[Color] = "Blue"
    )
RETURN
    IF (
        ISBLANK ( BluePercent ),
        CALCULATE ( MIN ( T[%] ), ALLEXCEPT ( T, T[Product] ) ),
        BluePercent
    )