cancel
Showing results for
Did you mean:
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

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

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

Thanks,

Bee Kee

1 ACCEPTED SOLUTION
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.

2 REPLIES 2
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.

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?

Announcements

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 Design Challenge

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

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.