Forum Discussion
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 | S1 | S2 | S3 |
| Mobile | 113 | 113 | 114 |
| Laptop | 1133 | 1133 | 1134 |
| Desktop | 2211 | 1122 | 2211 |
| Furniture | 1101 | 1102 | 1101 |
Thanks
Syed
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 _resultit works like:
hello Iam_Syed
for subtotal, just tick either column or row subtotal or both depend on your need.
Thank you.
12 Replies
- FreemanZ
Super User
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 _resultit works like:
- Irwan
Super User
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_SyedRegular Visitor
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])))ReturnIF(_Duplicate>1,"Green","Red")ThanksSyed
- v-kpoloju-msft
Community Support
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
Community Support
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.