Forum Discussion
DAX formula for Matrix visual
Example to undersatnd the data:-
60 is the total Model Allocated capacity of Models 855 and N14 both under S/N 1. Similarly 72 is Model Allocated capacity of Models QSM15 under S/N 2.
Question:-
Total demand on 24-09-02 for S/N is 47 but total allocated capacity is 60. So, demand is less than capacity.
If total demand is less than allocated capacity then output should be 0, if it is greater than allocated capacity then output should be 1. Ignore Backlog in it.
I want to use conditional formatting "Background color" in Matrix visual. So, I will denote 0 as red, 1 as green in background color.
Please help me in it.
First, create a calculated measure that will evaluate if the demand exceeds the allocated capacity or not. Use the following logic in the DAX formula:
DemandVsCapacity =
IF(
SUM(SalesTable[Total Demand]) > SUM(SalesTable[Model Allocated Capacity]),
1,
0
)- eplace SalesTable with the name of your table, and Total Demand and Model Allocated Capacity with the corresponding column names.
Conditional Formatting in Matrix Visual:
After creating the DAX measure DemandVsCapacity, add it to your Matrix visual.
Go to the Values area of the Matrix visual, click the dropdown next to the new measure DemandVsCapacity, and select Conditional Formatting > Background Color.
In the conditional formatting settings, choose Field Value and select the measure DemandVsCapacity.
Define the colors for the output:
- For 0 (Demand less than Capacity): Choose Red.
- For 1 (Demand greater than Capacity): Choose Green.
Now, based on the comparison of demand and capacity, your Matrix visual will show cells with red or green backgrounds.
If you have still issue with this problem pleaes feel free and contact with me.
1 Reply
First, create a calculated measure that will evaluate if the demand exceeds the allocated capacity or not. Use the following logic in the DAX formula:
DemandVsCapacity =
IF(
SUM(SalesTable[Total Demand]) > SUM(SalesTable[Model Allocated Capacity]),
1,
0
)- eplace SalesTable with the name of your table, and Total Demand and Model Allocated Capacity with the corresponding column names.
Conditional Formatting in Matrix Visual:
After creating the DAX measure DemandVsCapacity, add it to your Matrix visual.
Go to the Values area of the Matrix visual, click the dropdown next to the new measure DemandVsCapacity, and select Conditional Formatting > Background Color.
In the conditional formatting settings, choose Field Value and select the measure DemandVsCapacity.
Define the colors for the output:
- For 0 (Demand less than Capacity): Choose Red.
- For 1 (Demand greater than Capacity): Choose Green.
Now, based on the comparison of demand and capacity, your Matrix visual will show cells with red or green backgrounds.
If you have still issue with this problem pleaes feel free and contact with me.