Change color based on TOTAL

[giuliapiazza94]

Hi guys,

i need to change color of measure based on the total.

For Example:

	Customers	Total Sales		Sales Item X	% item X		Sales Item Y	% item Y
	A	100		20	20%		80	80%
	B	500		300	60%		200	40%
	C	120		10	8%		110	92%
	D	60		40	67%		20	33%
	E	430		400	93%		30	7%
Total		1210		770	64%		440	36%

item X = the values 20%, 60%, 8% are red because are less then 64%

               the values 67% and 93% are green because are grater then 64%

item Y = the value 7% is red because is less then 36%

              the values 80%, 40%, 92% and 33% are green because are grater then 36%

How can I set the color?

Thank you all 😁

[v-yiruan-msft · Accepted solution]

Hi @giuliapiazza94 ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can follow the steps below to get it:

1. Create two measures as below

XConditional formatting = 
VAR _x =
    CALCULATE ( SUM ( 'Table'[Sales Item X] ), ALLSELECTED ( 'Table'[Customers] ) )
VAR _y =
    CALCULATE ( SUM ( 'Table'[Sales Item Y] ), ALLSELECTED ( 'Table'[Customers] ) )
VAR _total = _x + _y
VAR _xper =
    DIVIDE ( _x, _total )
RETURN
    IF ( [% item X] < _xper, "red", "green" )

YConditional formatting = 
VAR _x =
    CALCULATE ( SUM ( 'Table'[Sales Item X] ), ALLSELECTED ( 'Table'[Customers] ) )
VAR _y =
    CALCULATE ( SUM ( 'Table'[Sales Item Y] ), ALLSELECTED ( 'Table'[Customers] ) )
VAR _total = _x + _y
VAR _yper =
    DIVIDE ( _y, _total )
RETURN
    IF ( [% item Y] < _yper, "red", "green" )

2. Configure conditional formatting

If the above one can't help you get the expected result, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards