Dynamic count of equal values per column and cond formatting when lower then % of column total

[macas01]

Hello All,

I have a raw data table roughly similiar to the folliwing one:

Item	Category	Attribute	Value
VE1	WHITE	F_01	1234AA
VE1	WHITE	F_06	AA112
VE1	WHITE	F_05	BB112
VE2	WHITE	F_01	1234AA
VE2	WHITE	F_06	AA112
VE2	WHITE	F_05	BB112
VE7	WHITE	F_05	BB112
VE7	WHITE	F_06	AA112
VE7	WHITE	F_01	1234AD
VE8	RED	F_04	1234AA
VE8	RED	F_01	1234AA
VE8	RED	F_02	REP_2
VE6	RED	F_02	REP_2
VE6	RED	F_04	1234AA
VE6	RED	F_01	1234AA
VE3	BLUE	F_01	1234AC
VE3	BLUE	F_05	BB112
VE3	BLUE	F_03	XYZ1
VE4	BLUE	F_01	1234AB
VE4	BLUE	F_05	GG14
VE4	BLUE	F_03	XYZ2
VE5	BLUE	F_01	1234AC
VE5	BLUE	F_05	BB112
VE8	RED	F_05	GG14
VE8	RED	F_06	AA101

So basically an unpivoted source data table.

And i am showing this data on a Matrix Visual where

Rows: [Category], [Item]

Columns: [Attribute]

Values: [Value]

With this results:

Item	Category	F_01	F_02	F_03	F_04	F_05	F_06
VE1	WHITE	1234AA				BB112	AA112
VE2	WHITE	1234AA				BB112	AA112
VE3	BLUE	1234AC		XYZ1		BB112
VE4	BLUE	1234AB		XYZ2		GG14
VE5	BLUE	1234AC				BB112
VE6	RED	1234AA	REP_2		1234AA
VE7	WHITE	1234AD				BB112	AA112
VE8	RED	1234AA	REP_2		1234AA	GG14	AA101

What i need to do now is to highlight all the values, for each separate column, that show up below a fixed percentage of total count of that column.

Let's say this percentage is 25%, in this case i would have:

Item	Category	F_01	F_02	F_03	F_04	F_05	F_06
VE1	WHITE	1234AA				BB112	AA112
VE2	WHITE	1234AA				BB112	AA112
VE3	BLUE	1234AC		XYZ1		BB112
VE4	BLUE	1234AB		XYZ2		GG14
VE5	BLUE	1234AC				BB112
VE6	RED	1234AA	REP_2		1234AA
VE7	WHITE	1234AD				BB112	AA112
VE8	RED	1234AA	REP_2		1234AA	GG14	AA101

So just to be clear: referring to column F_06, there are 4 items in total in it and the value "AA101" is showing up 1/4 --> 25% so Highilight! Same thing for F_01: 1234AC is 2/8 -->25%, 1234AD and 1234AC are 1/8 -->12,5%.

And also it needs to change dinamically accordingly to Slicers i have in my dashboard ([Item], [Category]).

For example if are filtered only Item "VE3","VE4","VE5" and "VE7":

Item	Category	F_01	F_02	F_03	F_04	F_05	F_06
VE3	BLUE	1234AC		XYZ1		BB112
VE4	BLUE	1234AB		XYZ2		GG14
VE5	BLUE	1234AC				BB112
VE7	WHITE	1234AD				BB112	AA112

The path i am following right now is to create a measure, and then refer the conditional formatting of [Value] to that measure.

The measure should calculate the count of equal values per similar [Attribute] but i am struggling a little bit because my source table is unpivoted and i am not so good in table processing with DAX and i am losting in it ^^'

I have tried also creating a custom table using the summarize and the count function, link it to the main table, and refer the cond format to the count column of the summarized table. It workish, but it does not evolve accordingly to slicers, it's a fixed calculation and it's not what i need

Hope that everything is clear

Looking forward to have some suggestion

Thank you!!

[macas01]

Until now, i have found a way to calculate the total count for each column that change accordingly to filtered rows.

Measure = CALCULATE(COUNTAX(SUMMARIZE('Table','Table'[Item]),'Table'[Item]),ALLSELECTED('Table'[Item],'Table'[Category]))

So this will be my denominator on my overall measure which i will then apply the Conditional Formatting.

I am still figuring out the count of the equal values for each column

[macas01 · Accepted solution]

Ok, I figure it out by myself.

Probably is not the most efficient way, but it's working 😃. I post it here for whoever needs it.

This is the measure:

Measure = 
var tab = CALCULATETABLE(SUMMARIZE('Table','Table'[Value],"cnt",count('Table'[Value])),ALLSELECTED('Table'[Category],'Table'[Item],'Table'[Value]))
return
SUMX(tab,IF(SELECTEDVALUE('Table'[Value])='Table'[Value],[cnt],0))/CALCULATE(COUNTAX(SUMMARIZE('Table','Table'[Item]),'Table'[Item]),ALLSELECTED('Table'[Item],'Table'[Category]))

And then apply conditional formatting on "Value" as following:

This is the result ( i kept "Measure" in table only for sake of clarity):

And it change accordingly to filters:

[macas01]

@lkalawski here is a link to download the SampleBi.pbix 

Thanks

[lkalawski]

Hi @macas01 ,

In this example, the solution you mentioned is enough - measure and conditional formatting. If you have started to write a measure, please paste it here or add a sample file that I can use to write the measure.