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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

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 June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors