topic Re: How to exclude single cell from a column to calculate total in DAX Commands and Tips

How to exclude single cell from a column to calculate total

jesusbritog — Sat, 23 Jul 2022 12:57:04 GMT

Hi everyone,

Before starting thanks for the support. I have these tables: sales, products, stores, and one table with exceptions, which means a product is not going to be sold in a particular store, below the structure:

My question is, how can I in a matrix don't take the value for Product 2, and store 4 (an exception) to calculate the Avg total, in this case, the result must be 28, not 19 because product 2 is not for sale at the store 4, is it possible in the matrix? or using DAX?.

Thanks a lot for the support and time,

Regards

Re: How to exclude single cell from a column to calculate total

technolog — Sat, 23 Jul 2022 13:23:21 GMT

Hi!

You can try create measure like this:

Store4Average = AVERAGEX( 'Table', DIVIDE (SUM('Table'[store4] ) , COUNTROWS( FILTER( 'Table' , 'Table'[store 4] <> BLANK () ) ) )

Re: How to exclude single cell from a column to calculate total

Jihwan_Kim — Sat, 23 Jul 2022 13:41:54 GMT

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Sales avg: = VAR _alltable = ADDCOLUMNS ( SUMMARIZE ( Sales, 'Product'[Product], Store[Store] ), "@salesavg", CALCULATE ( AVERAGE ( Sales[Sales] ) ) ) VAR _exceptiontable = ADDCOLUMNS ( FILTER ( SUMMARIZE ( Sales, 'Product'[Product], Store[Store] ), 'Product'[Product] = "Product2" && Store[Store] = "Store4" ), "@salesavg", CALCULATE ( AVERAGE ( Sales[Sales] ) ) ) VAR _returntable = EXCEPT ( _alltable, _exceptiontable ) RETURN AVERAGEX ( _returntable, [@salesavg] )

Re: How to exclude single cell from a column to calculate total

technolog — Sat, 23 Jul 2022 14:20:21 GMT

I think that jesusbritog means blank value and don't mean Products 2.

Re: How to exclude single cell from a column to calculate total

jesusbritog — Sat, 23 Jul 2022 15:08:23 GMT

Hi Jihwan_Kim , thanks a lot!, just a couple of questions:

1 - could be Exceptions table a isolated table right? I mean, not neccesary related to product table in the model.

2 - The sales table could have more than 160 million rows, is this a problem for that measure?

Again I really apreciate your support!

Re: How to exclude single cell from a column to calculate total

jesusbritog — Sat, 23 Jul 2022 15:09:53 GMT

Thanks a lot technolog , but it is more like the other solution!, I apreciate your support!