How to compare the values of adjacent columns in a Matrix visualization

[yurongzhang]

hi all

I have one problem which let me headache for days and wish to seek help your support.

My scenario is quite simple, to compare the sales of certain product on a yearly basis in the matrix visualization. 

The table used called CarSales, and the contents as follows, the measure I created called Measure_GAP, coding as follow,

Measure_GAP =

VAR _CurrentDate =

     SELECTEDVALUE(CarSales[YearNbr])

VAR _PreviousDate =

    CALCULATE (

        MAX ( CarSales[YearNbr] ),

        ALLSELECTED ( CarSales[YearNbr] ),

        KEEPFILTERS ( CarSales[YearNbr]< _CurrentDate )

    )

VAR _ThisMonth =

    CALCULATE (sum(CarSales[Sales]),CarSales[YearNbr]=_CurrentDate)

VAR _PreviousMonth =

    CALCULATE (

        sum(CarSales[Sales]),

        CarSales[YearNbr] = _PreviousDate

    )

RETURN

    if (_PreviousMonth=BLANK(),_ThisMonth,

    _ThisMonth-_PreviousMonth+0)

And the result as below, 

there are two issue, 

No1, in case of _thismonth value is null, the Measure_GAP return is blank value. Even I check the Blank() is the measure statement, it always return blank value instead of the designed value I stipulate.

No 2, in case of Toyota, 2022 vs 2021 sales gap is 4200 instead of 6100.

Thanks in advance for solving the problem for me!

Best Regards

Yurong ZHANG

[Anonymous · Accepted solution]

Hi @yurongzhang,

Theses 'blank' cells should be due to your table records. If they not really include in your tables, the measure expressions will not calculate on not existent range and keep show blank results. (measure not able to work as category/axis fields to expand the calculations ranges, they can only calculate with current row context)

BTW, power bi also include an auto existed filter effect on visual to hide the field which not has correspond values. They has higher priority than your measure formulas calculation and will auto filter to hide these fields.

For this scenario, I'd like to suggest you check the row/column field which not has correspond records and create a new unconnected table with that field values to replace the raw field which matrix used.

After these steps, you can modify your formula to check current row and column value as condition and lookup and summary table records.

Regards,

Xiaoxin Sheng