Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am working on designing a solution and need help.
There are two facts table: Data and Target.
For certain KPIs, I need to compare Yearly/Monthly/Daily actual vs target. I am looking for applying conditional formatting using DAX. I am partial successful in the case if all the target comparision are either "Higher the better" or "Lower the Better". But if I want to dynamically apply the conditional formatting based on the filter selection, then how should I do it?
At present, I am using following DAX for conditional formating:
Revenue KPI Color (HTB) = SWITCH( TRUE() ,SUM(Data[KPI_Value]) > SUM(Target[KPI_Value]) , "#f44242" ,SUM(Data[KPI_Value]) < SUM(Target[KPI_Value]), "#5ff442", "#f44242" )
Revenue KPI Color (LTB) = SWITCH( TRUE() ,SUM(Data[KPI_Value]) < SUM(Target[KPI_Value]) , "#f44242" ,SUM(Data[KPI_Value]) > SUM(Target[KPI_Value]), "#5ff442", "#f44242" )
I have created a KPI table with the list of all available KPIs categories and sub-categories. I have included another column for flag for Lower the better. Using this table, I would like to drive the KPI color selection for Higher the Better or Lower the better.
Enclosing the sample pbix file here
Any help in this regard will be highly appreciated.
Thanks,
Vivek
Solved! Go to Solution.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêshI @vivran22 ,
You can use one of the two measure below to make your condittional formatting:
Conditional Low and High = SWITCH ( TRUE (); SUM ( Data[KPI_Value] ) > SUM ( Target[KPI_Value] ) && SELECTEDVALUE ( 'KPI'[Lower The Better] ) = 0; "#f44242"; SUM ( Data[KPI_Value] ) < SUM ( Target[KPI_Value] ) && SELECTEDVALUE ( 'KPI'[Lower The Better] ) = 0; "#5ff442"; SUM ( Data[KPI_Value] ) < SUM ( Target[KPI_Value] ) && SELECTEDVALUE ( 'KPI'[Lower The Better] ) = 1; "#f44242"; SUM ( Data[KPI_Value] ) > SUM ( Target[KPI_Value] ) && SELECTEDVALUE ( 'KPI'[Lower The Better] ) = 1; "#5ff442"; "#f44242" )
Conditional Low and High = IF(SELECTEDVALUE('KPI'[Lower The Better]) = 1 ; [Revenue KPI Color (LTB)]; [Revenue KPI Color (HTB)] )
Not sure if the order is correct based on your file but the colours change when compared to the visuals you have on your report.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
Hi,
Thank you for your response. I have tried using both the DAX shared but I am not able to select the DAX under Format by - Field Value option (Conditional Formating). The DAX appears to be greyed out or something like that.
Please advise.
Thanks,
Vivek
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |