cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vin26
Resolver I
Resolver I

Dynamically categorize and sum the matrix based on the slicers

Hello,

 

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:

PBI_6.JPG

 

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

PBI_7.JPG

 

7 REPLIES 7
amitchandak
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 
,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow"))
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
"red"
)

 

Color Field - Color Measure - Conditional formatting - Check Steps
https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
https://exceleratorbi.com.au/conditional-formatting-using-icons-in-power-bi/
https://community.powerbi.com/t5/Desktop/FORMAT-icon-set-for-use-in-a-data-card/td-p/811692

 

Also, explore SEGMENTATION

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

 

 

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.

 

 

AiolosZhao
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:

table1:

Type

Orange

Green

Blue

Yellow

 

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),

"Green".....

 

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 

AiolosZhao
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.

AiolosZhao
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

Announcements
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