dealing with blanks in measure?

[Dh0]

Hi I'm trying to create a couple of measures with the following data. I'd like to understand the difference in products sold by year and at different stores:

Product	Year	Qty Sold	Store Type
Apples	2023	20	Market
Oranges	2023	5	Market
Apples	2024	30	Market
Apples	2023	50	Store
Oranges	2023	23	Store
Apples	2024	55	Store
Oranges	2024	19	Store

I can use the following measures to look at the difference compared to previous year:

SumQty = sum(ProductTable[QtySold])

Diff2prevYr = 
[SumQty]- CALCULATE([SumQty],all(ProductTable[Year]),ProductTable[Year]=SELECTEDVALUE(ProductTable[Year])-1)

the measure in general works but I think because the data above has no row for oranges sold in 2024 in the market, this blank causes my diff measure to skip it. 

I was able to fix this by creating a separate table with distinct values of years and with 1:many relationship to my product table and using the new table in my measure, can force a value through:

Diff with YearsYr = 
[SumQty]- CALCULATE([SumQty],Years[Year]=SELECTEDVALUE(Years[Year])-1)

This works in my visual which is filtered on year 2024 and I can get all the bars showing:

The issue is I'm trying to sort the x-axis by a new measure which tries to sum up the total height of the bars. referring to image above I want the measure to return 15 for market and 9 for store. The measure I've created is:

AbsDiff_byStore = 
var temptable = CALCULATETABLE(SUMMARIZE(ProductTable,ProductTable[Product],"variance", 
    abs([SumQty] - CALCULATE([SumQty],ALLEXCEPT(ProductTable,ProductTable[Product],ProductTable[Store Type]),Years[Year]=SELECTEDVALUE(Years[Year])-1))),all(ProductTable[Product]))

return sumx(temptable,[variance])

Similar to beginning of this post, the measure works except for where there is a blank value, in which case the row is ignored (in the highlighted value I'm trying to get 15)

If anyone can point me in the right direction or provide any feedback that would be great! Thanks

[Dh0]

@DataNinja777 thanks a lot for posting. The coalesce function is interesting and I hadnt come across this before but unfortunately when trying to use your measure (using selectcolumns) or modifying my measure (with calculatetable) the rows with blank are still not considered - I think it's something earier in the measure that means that blank rows are not evaluated in the coalesce function. I've attached my pbix file for reference. Thanks!

PBIX file on GoogleDrive 

[v-zhangtin-msft · Accepted solution]

Hi, @Dh0 

I found the reason you were not able to succeed was a missing line of fields in the source data.

You can try the following methods. 

New ProductTable = UNION(ProductTable,'Table')

The next calculations are based on the new table and do not require the use of the year table.

Diff = 
SUM('New ProductTable'[QtySold])- CALCULATE(SUM('New ProductTable'[QtySold]),FILTER(ALLEXCEPT('New ProductTable','New ProductTable'[Product],'New ProductTable'[Store Type]),'New ProductTable'[Year]=SELECTEDVALUE('New ProductTable'[Year])-1))

ABSDIFF = ABS([Diff])

Measure = 
Var _table=SUMMARIZE(ALL('New ProductTable'),'New ProductTable'[Store Type],'New ProductTable'[Product],'New ProductTable'[Year],"ABS",[ABSDIFF])
RETURN
SUMX(FILTER(_table,[Store Type]=MAX('New ProductTable'[Store Type])&&[Year]=MAX('New ProductTable'[Year])),[ABS])

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dh0]

@v-zhangtin-msft thanks for the response - I had thought about creating a new table and adding in zeros - my actual dataset is much bigger so I was thinking along lines of crossjoining every combination of product/store/year then either try to pull in quantity sold or just declare zeros and do a union with original similar to what you have suggested. This does seem like overkill however?

[Dh0]

Just to follow up on this, I went along the lines of @v-zhangtin-msft and created an intermediate table with every variable combination and zero qty value and doing a union with my original source data. Not most elegant approach but the visual behaved as expected. Thanks a lot @v-zhangtin-msft  and @DataNinja777 for suggestions

[DataNinja777]

Hi @Dh0 ,

Your issue with blanks causing missing calculations in AbsDiff_byStore likely stems from how the measure treats missing data in your CALCULATETABLE function. When a product is absent for a given year-store combination, it results in a blank, which prevents the measure from correctly summing all variances. To resolve this, you need to ensure that missing values are explicitly handled as zeros before applying the absolute difference.

A refined approach involves modifying your measure to use COALESCE, which replaces blanks with zero before performing calculations. This ensures that all store types and product combinations are accounted for, even when no sales data exists for a particular year or store. Here’s how the adjusted measure should look:

AbsDiff_byStore = 
VAR temptable = 
    ADDCOLUMNS(
        SUMMARIZE(
            ProductTable, 
            ProductTable[Product], 
            ProductTable[Store Type]
        ),
        "variance", 
        ABS(
            COALESCE([SumQty], 0) - 
            COALESCE(
                CALCULATE(
                    [SumQty], 
                    ALLEXCEPT(ProductTable, ProductTable[Product], ProductTable[Store Type]), 
                    Years[Year] = SELECTEDVALUE(Years[Year]) - 1
                ), 
                0
            )
        )
    )

RETURN 
    SUMX(temptable, [variance])

This measure works by first summarizing all combinations of Product and Store Type, ensuring that even missing rows are included. It then calculates the absolute difference between the current year's quantity and the previous year's quantity, replacing any blank values with zero. By applying COALESCE, the calculation avoids cases where a missing product in a given year causes the measure to ignore that row entirely.

With this adjustment, your visual should now correctly return 15 for Market and 9 for Store, ensuring that missing values no longer cause inconsistencies in the sorting logic.

Best regards,