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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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