Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
MadhuKumar
Helper II
Helper II

Unable to show same percentages for all levels in Matrix

Hi,

I have created a matrix table used Takepoints, Job name as Rows and Year,Month and days in to columns shows demand in to values for each level and daywise as shown in attached screenshot. I want to create a conditional formatting based on demand percentage like below 50% "Red", 50-75% "Green", 75-100% "yellow". But when i am using some dax calculations the data is splitting for each level, Ex: Takepoint wise demand% is 80 whereas i am drill-down to the job level it is splitting in to 40-40%, this is causing the conditional formatting shows incorrect. I need same 80% for all levels when i am drill-down to the next level in matrix then conditional formatting will shows correct, the percenatges should not split at any level of hierachy. Please let me know whether this can be possible or not? If possible suggest me how can i acheive this.

 

DAX calculations used:

 

1) Demand Percentage Consistent =
DIVIDE(
   SUM('TableName'[Demand]),
   CALCULATE(SUM('TableName'[Total Demand]), ALLSELECTED('TableName'))
)

2) Demand Percentage Consistent =
VAR TotalDemand = CALCULATE(
   SUM('DemandTable'[Demand]),
   ALLSELECTED('TakePointTable'),
   ALLSELECTED('JobTable'),
   ALLSELECTED('DateTable')
)
RETURN
DIVIDE(
   SUM('DemandTable'[Demand]),
   TotalDemand,
   0
)

3) Percentage =
DIVIDE(
   SUM(Sales[Sales Amount]),
   CALCULATE(
       SUM(Sales[Sales Amount]),
       ALLEXCEPT(Sales, Sales[Takepoint Name])
   )
)

 

Image (1).jfif

 

Image.jfif

 

 

 

 

 

3 REPLIES 3
MFelix
Super User
Super User

Hi @MadhuKumar ,

 

You should use the ALLSELECTED at the level of the aggregated column you want to see and not at the level of the entire table because if you have any additional levels they will get calculated accordingly.

 

For example if your hierarchy is Country / Region when you do ALLSELECTED of the full table you will only pick up the Region when you are at drill down level, however if you use it at ALLSELECTED (Table[Country]) this will pick up all the values for the specific country no matter what level you are at.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



If possible can you please modify the dax and provide the new formula. I need to show conditional formatting for first level(Takepoint) and when drill-down to the next level(Job) it shows same percentage and it should not split.

 

Is this scenario possible in Matrix?

 

 

Hi @MadhuKumar ,

Base on your description, you create a matrix visual with multiple levels. And you want to configure conditional formatting on it base on [Takepoint name] percentage even though the value for [job] level. It seems the fields on the matrix visual comes from multiple tables(TakePointTable,DemandTable, DateTable and JobTable), could you please provide some sample data(exclude sensitive data), the relationships and matrix Fields settings?  It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

 

In additional, you can refer the following link to try to figure out.

Highlighting the minimum and maximum values in a Power BI matrix - SQLBI

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.