Forum Discussion

Iam_Syed's avatar
Iam_Syed
Regular Visitor
7 months ago
Solved

When values duplicate then green when unique then Red

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)                      ...
  • FreemanZ's avatar
    FreemanZ
    7 months ago

    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's avatar
    Irwan
    7 months ago

    hello Iam_Syed 

     

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

     

    Thank you.

Irwan's avatar
Irwan
Icon for Super User rankSuper User
7 months ago

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.