Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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])
)
)
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsIf 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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
146 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
218 | |
88 | |
73 | |
64 | |
60 |