When values duplicate then green when unique then Red

[Iam_Syed]

Hi Team,

I am stuck for creating a calculation but lo luck, please help on this.

In this table has Row = Category (Text)

                          Column = Type (Text)

                          Values = sum of sales (Numeric)

I want to show this table values when duplicate then Green color if Unique the Red color.                           

Category	S1	S2	S3
Mobile	113	113	114
Laptop	1133	1133	1134
Desktop	2211	1122	2211
Furniture	1101	1102	1101

Thanks

Syed

[FreemanZ · Accepted solution]

hi @Iam_Syed ,

Not sure if i fully get you, supposing you have a data table like:

try to plot your expected matrix visual and apply conditional formatting for the value field background based field value with a measure like below:

Measure = 
VAR _cat = MAX(data[category])
VAR _type = MAX(data[type])
VAR _table =
    ADDCOLUMNS(
        SUMMARIZE(
            FILTER(
                ALL(data),
                data[category] = _cat && data[type] <> _type
            ),
        data[category], data[type]
        ),
        "Amt",
        CALCULATE(SUM(data[sales]))
    )
VAR _AmtList = SUMMARIZE(_table, [Amt])
VAR _result = IF([SalesAmt] IN _AmtList, "Green", "Red")
RETURN _result

it works like:

[v-kpoloju-msft]

Hi @Iam_Syed, 

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @Irwan, @techies, @FreemanZ,  for those inputs on this thread.

Has your issue been resolved? If the response provided by the community member @Irwan, @techies, @FreemanZ,  addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[v-kpoloju-msft]

Hi @Iam_Syed, 

Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[techies]

Hi @Iam_Syed Based on the current logic (see screenshot attached), the subtotal is highlighted green when that category contains duplicate values across types.

Is this the expected behavior?

[FreemanZ · Accepted solution]

hi @Iam_Syed ,

Not sure if i fully get you, supposing you have a data table like:

try to plot your expected matrix visual and apply conditional formatting for the value field background based field value with a measure like below:

Measure = 
VAR _cat = MAX(data[category])
VAR _type = MAX(data[type])
VAR _table =
    ADDCOLUMNS(
        SUMMARIZE(
            FILTER(
                ALL(data),
                data[category] = _cat && data[type] <> _type
            ),
        data[category], data[type]
        ),
        "Amt",
        CALCULATE(SUM(data[sales]))
    )
VAR _AmtList = SUMMARIZE(_table, [Amt])
VAR _result = IF([SalesAmt] IN _AmtList, "Green", "Red")
RETURN _result

it works like:

[Irwan]

hello @Iam_Syed 

please check if this accomodate your need.

1. i assumed your original table looks like the one you screenshot above, then you need to unpivot your table.

2. create a measure for checking the duplicate and coloring

Color Palette =
var _Duplicate = 
CALCULATE(
    COUNT('Table'[Category]),
    FILTER(
        ALL('Table'),
        'Table'[Category]=SELECTEDVALUE('Table'[Category])&&
        'Table'[Value]=SELECTEDVALUE('Table'[Value])
    )
)
Return
IF(
    _Duplicate>1,
    "Green",
    "Red"
)

3. create a matrix visual then assign the measure for conditional formating.

4. in conditional formating, choose Field value in Format style then use the measure for the field.

Hope this will help.
Thank you.

[Iam_Syed]

Hi Irwan,

Thanks for quick reply,

When I apply this calculation in my original table, all rows getting green,

Color Palette =

var _Duplicate =

CALCULATE(

    COUNT('SDSA047 Sales History Analysis (As Reported) - PBI'[Profit Center]),

    FILTER(

        ALL('SDSA011 High Level Analysis (as Reported) - PBI'),

        'SDSA011 High Level Analysis (as Reported) - PBI'[Profit Center]=SELECTEDVALUE('SDSA011 High Level Analysis (as Reported) - PBI'[Profit Center])&&

        'SDSA011 High Level Analysis (as Reported) - PBI'[INV VAL]=SELECTEDVALUE('SDSA011 High Level Analysis (as Reported) - PBI'[INV VAL])

    )

)

Return

IF(

    _Duplicate>1,

    "Green",

    "Red"

)

Thanks

Syed

[Irwan]

Hello @Iam_Syed 

The logic should be fine.

Can you share a screenshot of your result where you said all green?

Thank you.

[Iam_Syed]

Hi Irwan,

Anyways I got the calculation in Copilot where I need to create 3 measures, 

1 Rounded 2 Count of Duplicates 3 Color flag.  thanks you can close this.

[Iam_Syed]

need to show in subtotal

[Irwan]

hello @Iam_Syed 

i see your result has already good, it shows green for same value while red shows the different value. except the zero and blank value.

here i made a little bit changes with some blank and zero value.

- previous (exact same DAX as before), blank and zero show green.

- changes (add more filter). the additional syntax is colored red in DAX below.

Color Palette =
var _Duplicate = 
CALCULATE(
    COUNT('Table'[Category]),
    FILTER(
        ALL('Table'),
        'Table'[Category]=SELECTEDVALUE('Table'[Category])&&
        'Table'[Value]=SELECTEDVALUE('Table'[Value])&&
        not (
        'Table'[Value]=0||
        ISBLANK('Table'[Value])
        )
    )
)
Return
IF(
    _Duplicate>1,
    "Green",
    "Red"
)

Hope this will help.

Thank you.

[Iam_Syed]

Hi Irwan,

We need that 0 values the table it selft will calculate right, if duplicate then Green or If unique the Red. that is fine what we expected,

I wanted to show the subtotal as well 

[Irwan · Accepted solution]

hello @Iam_Syed 

for subtotal, just tick either column or row subtotal or both depend on your need.

Thank you.