Forum Discussion

Iam_Syed's avatar
Iam_Syed
Regular Visitor
6 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)

                          Values = sum of sales (Numeric)

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

 

Category S1S2S3
Mobile113113114
Laptop113311331134
Desktop221111222211
Furniture110111021101

 

 

Thanks

Syed

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

    hello Iam_Syed 

     

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

     

    Thank you.

12 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    6 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
    Icon for Super User rankSuper User
    6 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.

    • Iam_Syed's avatar
      Iam_Syed
      Regular Visitor
      6 months ago

      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's avatar
        Irwan
        Icon for Super User rankSuper User
        6 months ago

        Hello Iam_Syed 

         

        The logic should be fine.

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

         

        Thank you.

  • techies's avatar
    techies
    Icon for Super User rankSuper User
    6 months ago

    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?

     

     

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Icon for Community Support rankCommunity Support
    6 months ago

    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's avatar
      v-kpoloju-msft
      Icon for Community Support rankCommunity Support
      6 months ago

      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.