cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
beekee
Helper IV
Helper IV

ALL function in Matrix Table

Hi All,

 

I have two charts: treemap and matrix table. In the matrix table, I have YoY% and icon column which is used to indicate whether the individual YoY% is greater or lower than the total growth. 

 

Logic for icon column

var total = CALCULATE( [YoY%], ALL(Table1[Area]), ALL(Table1[City]) )
return
IF( [YoY%] >= total, 1, -1 )

 

1 = Arrow up
-1 = Arrow down

 

As I need to include ALL function in the measure (shown above), hence whenever I select the treemap, the icon column will be wrong as the total growth is not changing. 

 

Before filtering on Treemap

total growth is 1.14%, icon is showing correctly

beekee_0-1593530432027.png

 

 

After filtering the Treemap

total growth is 63.18%, icon not correct (e.g. City Z should be arrow up as -55.46% > -63.18%)

after filter.PNG

 
 

 

 

 

 

 

 

Does anyone know how to show the icon in the correct direction?

 

 

Thanks,

Bee Kee

 

 

 

 
 
1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@beekee I think you should use ALLSELECTED instead of ALL

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

2 REPLIES 2
parry2k
Super User
Super User

@beekee I think you should use ALLSELECTED instead of ALL

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





jthomson
Solution Sage
Solution Sage

As you're using all, it's going to factor in everything, so will still compare to the -1.something% - maybe try using allselected to see if it recalculates it based just on the subset of what you have filtered?

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!