How to filter out blanks from column in Pivot Table (Data Model)

[63falcondude]

Hello all,

I can't seem to figure out how to remove rows from a pivot table that was built from a data model.
I want to remove the rows where "Sum of Sales" is blank.

I tried selecting one of the SKU Descriptions in the pivot > Filter > Value Filters > Sum of Sales greater than 0, but that does not remove the blanks.

Please see attached.

Thank you in advance!

[popipipo]

You have to use only table4 with the extra column

[Bo_Ry]

New Measure: Sum_Inventory

=if(sum(Sales[Sales]),sum(Inventory[Inventory]))

[63falcondude]

Thanks for this! We're almost there.
Is there a way to get the subtotals to sum correctly when the pivot table is collapsed?

[63falcondude]

You have to use only table4 with the extra column

Thank you for the reply but bringing inventory into the sales table like that will not work for my applications.
My apologies for not providing a better example that shows this.
I have other columns that make the Store and SKU columns duplicated so bringing Inventory into the sales table would result in the pivot table showing higher Inventory than there actually is.

[Bo_Ry]

Please try

=SUMX(ADDCOLUMNS(Sales,"Inv",CALCULATE(sum(Inventory[Inventory]))),[Inv])

[63falcondude]

You sir are a legend.

Seriously, I can't thank you enough!

I tried to give you a rep but it says that I have to spread some out before I can give you more...

I have to check to make sure that the Inventory numbers are not being duplicated but will report back with a more representative sample if they are.

[63falcondude]

Sorry about that! Here is a more representative sample.

Since I have additional columns in my sales data, the inventory was being counted multiple times in the pivot.
I updated the sales data sample to show this.

Please see column O for what column N should show.

[Bo_Ry]

Try

=SUMX(GROUPBY(ADDCOLUMNS(Sales,"Inv",CALCULATE(sum(Inventory[Inventory]))),[Store],[SKU],[Inv]),[Inv])

[63falcondude]

That did it!

I put it into my actual report and it worked flawlessly.

Thank you so much (again). I wish I could rep ya...