Showing results for 
Search instead for 
Did you mean: 
Resolver I
Resolver I

Dynamically categorize and sum the matrix based on the slicers



I have a matrix which need to be categorized and summarized based on the slicer selections, these slicers do not have relationship with main table. I need to sum up the values, I have slicers and matrix like this:



I need get the dynamic table like below on the selection:



Super User
Super User

@vin26 , not very clear. But you can create a color measure and use that in conditional formatting using "Field" option.


Color  = if(FIRSTNONBLANK(Table[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170 
Color sales = if([Sales Today] -[sales yesterday]>0,"green","red")

color =
switch ( true(),
FIRSTNONBLANK(Table[commodity],"NA") ="commodity1" && sum(Table[Value]) >500,"lightgreen",
FIRSTNONBLANK(Table[commodity],"NA") ="commodity2" && sum(Table[Value]) >1000,"lightgreen",
/// Add more conditions


Color Field - Color Measure - Conditional formatting - Check Steps


Also, explore SEGMENTATION



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hi @amitchandak  Thanks for your response, conditional formatting part is done and it working fine. Please refre to the .pbix file here . My challenge is to sum up those colored range values, basically it is a dynamic segmentation in which user will have option to make the segmentation by selecting the slicer and those summed values will be used access the dashboard. Here, I need to create a measure which calculating total of each color.



Memorable Member
Memorable Member

Hi @vin26 ,


Could you please share your sample data pbix to me?


Because it takes too much time to create the data source.


And I think the measure won't be hard.


In my mind, firstly you can create a new table which has four values, like:








Then put it in a table, and create a measure like below:

measure = switch(Type,

"Orange",calculate(sum(your_value),filter(your_table,col <= max(your_orange_slicer_col) && col >= min(your_orange_slicer_col) && row <= max(your_orange_slicer_row) && row >= min(your_orange_slicer_row),



something like above.


Aiolos Zhao

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

HI @AiolosZhao  Thank you for the quick response, please find the sample .pbix file here 

Memorable Member
Memorable Member

Hi @vin26 ,


Something like this:

Measure = SWITCH(MAX('Table'[Column1]),
                    "Blue",CALCULATE(SUM(Sheet1[Values]),FILTER(Sheet1,Sheet1[Column 2] <= MAX(Table_Blue_Col[Blue_Col]) && Sheet1[Column 2] >= MIN(Table_Blue_Col[Blue_Col]) && Sheet1[Column 1] >= MIN(Table_Blue_Row[Blue_Row]) && Sheet1[Column 1] <= MAX(Table_Blue_Row[Blue_Row]))),
                    "Green",CALCULATE(SUM(Sheet1[Values]),FILTER(Sheet1,Sheet1[Column 2] <= MAX(Table_Green_Col[Green_Col]) && Sheet1[Column 2] >= MIN(Table_Green_Col[Green_Col]) && Sheet1[Column 1] >= MIN(Table_Green_Row[Green_Row]) && Sheet1[Column 1] <= MAX(Table_Green_Row[Green_Row]))),
                    "Orange",CALCULATE(SUM(Sheet1[Values]),FILTER(Sheet1,Sheet1[Column 2] <= MAX(Table_Orange_Col[Orange_Col]) && Sheet1[Column 2] >= MIN(Table_Orange_Col[Orange_Col]) && Sheet1[Column 1] >= MIN(Table_Oranage_Row[Orange_Row]) && Sheet1[Column 1] <= MAX(Table_Oranage_Row[Orange_Row]))),
                    "Yellow",CALCULATE(SUM(Sheet1[Values]),FILTER(Sheet1,Sheet1[Column 2] <= MAX(Table_Yellow_Col[Yellow_Col]) && Sheet1[Column 2] >= MIN(Table_Yellow_Col[Yellow_Col]) && Sheet1[Column 1] >= MIN(Table_Yellow_Row[Yellow_Row]) && Sheet1[Column 1] <= MAX(Table_Yellow_Row[Yellow_Row]))))


Dynamically categorize and sum the matrix based on the slicers.PNG


Aiolos Zhao

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Hi @AiolosZhao  thanks lot for the solutions, it works as expected!!. you are master. Really amazing.

Memorable Member
Memorable Member

Glad to help you~



Aiolos Zhao

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helpful resources

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors