Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
yurongzhang
New Member

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

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,

yurongzhang_0-1685506446789.png

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.
yurongzhang_2-1685506769930.png

 

Thanks in advance for solving the problem for me!

 

Best Regards

Yurong ZHANG


 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.