DAX formula to get the correct count

[tonyclifton]

Hello community,

in below image I have an overall status for a product (47%). Now I'm looking for a formula that will give me the according color (Red) for that number which I only get to work with a measure in a card visual for example.

Hoewever in the pie chart I need to use a column which should only show one color for each product.

This is my statement for the Color and ColorMeasure column in the table above:

Color = SWITCH (
    TRUE ();
    [Status in %] >= 0 &&  [Status in %] < 0,8; "Red";
    [Status in %] >= 0,8 &&  [Status in %] < 1; "Yellow";
    [Status in %] = 1; "Green"; BLANK())

Status in % = 
var statusResult = CALCULATE (
    COUNTROWS ( 'Product' )
    )

var init =  DIVIDE(CALCULATE(sum('Product'[Status]));100)
var result =  CALCULATE(DIVIDE(init;statusResult))
return result

What do I need to do to get the pie chart working correctly?

Thank you.

[v-lili6-msft · Accepted solution]

hi, @tonyclifton 

If you could try this way:

Step1:

Add a dim color table(Don't create the relationship with any table)

Step2:

add a new measure like this:

Measure 3 = IF(SELECTEDVALUE('Dim Color'[Color])=[Color Measure],[Status in %])

Step3:

For pie chart, drag color field from dim color table and the measure into pie chart.

Result:

and here is pbix file, please try it.

If not your case, please share your sample data and your expected output form.

Best Regards,

Lin