Calculate Difference Between All Matrix Values Listed Against One Field Value For the Given Row

[Anonymous]

Hello,

I've created a Matrix which shows Average Production Qty in the "Values" section of the Matrix along with Week Start Number for the "Columns" section followed by Plant Name, Production Line/Line Type, Baseline(numeric) and Target(numeric) in the "Rows" section of the Matrix. I want to calculate the differences between each of the Average Production Qtys("Values" section) shown in the Matrix against the Target("Rows" section). The Baseline(numeric) and Target(numeric) fields are brought in from the Excel source as is and not a calculated measure created with a DAX aggregation formula.

Tables and Modeled Relationships
Average Production Qty - Fact Table (Plant and Production Line/Line Type level of granularity)
Week Start Number - Date Dim joined to the Fact Table (1:M) on Date Value
Plant Name - Fact Table (Plant and Production Line/Line Type level of granularity)
Production Line/Line Type - Fact Table (Plant and Production Line/Line Type level of granularity)
Baseline - Excel Xref source used as a dimension joined to the Fact Table (1:M) (Plant and Production Line/Line Type level of granularity)
Target - Excel Xref source used as a dimension joined to the Fact Table (1:M) (Plant and Production Line/Line Type level of granularity)

The Excel Xref joins to the Fact Table by Plant and Production Line/Line Type

Below is a screenshot of what I've created:

I want to calculate the differences between 928(W31 07/28) minus 1,414....981(W32 08/04) minus 1,414....etc. within the first row. Then for the second row use 1,414 for that respective row and calculate the difference between all the Matrix values listed for each Week Number...this continues on for each row. The end goal is to use these differences calculated for each Week Number("Columns" section) at the Row level and then use these Difference values to set the background color for the existing Matrix values. If the Matrix value is above the Target value then set the background color to green, if below then red. I WON'T be adding these Difference values to the Matrix but ONLY for setting the background color rules, possible.

Is this sort of task possible?

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could create a measure to set color in the conditional formatting( you could change color with color codes like "#FF00FF" ).

Measure =
IF (
    SELECTEDVALUE ( 'Table'[Target] ) > SELECTEDVALUE ( 'Table'[Value] ),
    "red",
    "green"
)

You could add this measure here.

Here is the result.

[Anonymous · Accepted solution]

This is the other DAX solution used to calculate the differences to drive the conditional formatting...

Summary Differences = CALCULATE('HANA Production Fact'[Avg Prod Qty Per Total Shifts by Week]-MINX('Excel Production Throughput Baselines Targets','Excel Production Throughput Baselines Targets'[Avg. Target]),FILTER('HANA Production Fact',RELATED('Excel Production Throughput Baselines Targets'[Plant Number and Production Line])='HANA Production Fact'[Plant Number and Production Line]))

There's only one [Avg. Target] value to be returned so either the MINX() or MAXX() functions can be used here.

[Anonymous]

@v-eachen-msft- Thank you for your solution you presented. I didn't try your solution as I figured out another way already but I'll accept your solution as another option for solving the problem.

[pa_trick67]

An attach pbi file would be helpful.

[Anonymous]

@pa_trick67Thank you for looking into this but I figured out my problem. Additionally, I can't share my pbix file because of the sensitivity of the data contained in it.

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could create a measure to set color in the conditional formatting( you could change color with color codes like "#FF00FF" ).

Measure =
IF (
    SELECTEDVALUE ( 'Table'[Target] ) > SELECTEDVALUE ( 'Table'[Value] ),
    "red",
    "green"
)

You could add this measure here.

Here is the result.