topic Re: Dax calculate total amount ignoring filter in DAX Commands and Tips

Dax calculate total amount ignoring filter

NT2 — Wed, 27 Nov 2024 21:23:54 GMT

Hello, I am working with a Power BI report and have a fact table that is related to two dimension tables: store and product. The fact table contains the following columns:

store (foreign key to store table),
product (foreign key to product table),
amount.

I need to create a measure that calculates the total sum of amount by store, regardless of the product or any other table, values in the visual. Specifically:

For any value in store, I want to see the sum of the amount column for all rows related to that store, ignoring the product filter

Example:

Given the following data in the fact table:

store product amount

Store A	Product X	5
Store A	Product Y	8
Store B	Product Z	3
Store A	Product X	8
Store A	Product Y	8

Need to put in the visual column from store, product and the measure
For store = Store A, the total amount should be: 5 + 8 + 8 + 8 = 29, which is the sum of all rows where store = Store A, regardless of the selected product.
For store = Store B, the total amount should be: 3, as there is only one row where store = Store B.

But when I created a measure using all products, I am getting incorrect result, where each store is multiplied by each product row. This is not showing the real products by store.

I have tried using CALCULATE with ALLEXCEPT, but the results are not as expected. The total still appears to be filtered by product in the visual, causing incorrect totals.

Can anyone help me achieve the correct DAX measure to get the total sum by store while ignoring product filters and preventing the incorrect multiplication of rows in the visual.

Expected Output Table:

Store Product Total Amount by Store

Store A Product X 29

Store A Product Y 29

Store B Product Z 3

Re: Dax calculate total amount ignoring filter

FreemanZ — Wed, 27 Nov 2024 23:29:47 GMT

hi NT2 ,

try like:

measure =

CALCULATE(

SUM(fact[amount]),

ALL(fact),

VALUES(fact[store])

)

Re: Dax calculate total amount ignoring filter

VahidDM — Wed, 27 Nov 2024 23:59:29 GMT

Hi NT2

Try this:

Total Amount by Store = CALCULATE( SUM('FactTable'[amount]), REMOVEFILTERS('Product') )

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

Appreciate your Kudos!!

LinkedIn|Twitter|Blog |YouTube

Re: Dax calculate total amount ignoring filter

NT2 — Thu, 28 Nov 2024 00:20:13 GMT

Hello, yes I tried this but this code caused each store to be multiplied by each row of products table, I had each store 6 times because in the table product I have 6 rows products, while I should see in the visual only 3rows (store A with product x another row with product y, store B with product Z) based on the example I provided

Re: Dax calculate total amount ignoring filter

NT2 — Thu, 28 Nov 2024 00:21:42 GMT

Hello, I already tested this an I did not get the result I was waiting for.

Re: Dax calculate total amount ignoring filter

VahidDM — Thu, 28 Nov 2024 00:40:44 GMT

Hey NT2

Can you try this:

Total Amount by Store = CALCULATE( SUM('FactTable'[amount]), FILTER( ALL('FactTable'), 'FactTable'[store] = MAX('FactTable'[store]) ) )

if it works, then you can change the ALL('FactTable') to columns to improve the performance

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

Appreciate your Kudos!!

LinkedIn|Twitter|Blog |YouTube

Re: Dax calculate total amount ignoring filter

FreemanZ — Thu, 28 Nov 2024 01:24:32 GMT

hi NT2 ,

could you tell more about "each store is multiplied by each product row"?

Re: Dax calculate total amount ignoring filter

NT2 — Thu, 28 Nov 2024 10:53:32 GMT

Hello, I was going to reply but I click on a accept as solution by mistake, the measure you provided me did not multiply each store by product but the amount is not what I needed it gave me the amount by product and store.

When I said caused each store to be multiplied by product I meant :

I put the product from dimension product and store from dimension store ok and a new measure I tested by remove filters and all for product you see each store I have in the fact I saw in the visual many rows 6 times for each product I have in the product dimension.

Thank you a lot for your help.

Re: Dax calculate total amount ignoring filter

NT2 — Thu, 28 Nov 2024 16:12:08 GMT

Thank you for you help, I think this will works for me, because now the Store is not multiplied by the product but I still have a problem the total I see in the meaure is not correct. I tested with sumx but did not gave me 29+29+3.

I made some adjustment to the model now the concatenation of column location and store is the primary key of the table store, so in the fact table I added a new column Store and location and now I use this column in the measure you provided me but now I see the amount by store and location and I want the total to be by Store even if we have many location.

Thank you again for your help.

Re: Dax calculate total amount ignoring filter

Rupak_bi — Thu, 28 Nov 2024 13:35:53 GMT

hi NT2 ,

Are you searching for a result like this??

if yes, than accept as solution.

Re: Dax calculate total amount ignoring filter

NT2 — Thu, 28 Nov 2024 15:48:47 GMT

Hello yes I need something like that but the column customer and product need to be taken from the dimension not the fact table because I tried to work with allexpect the amount I got wa multiplied by each product. Th final total need to be 29+29+3.

Thank you a lot for your answer.

Re: Dax calculate total amount ignoring filter

Rupak_bi — Thu, 28 Nov 2024 16:56:02 GMT

Hi NT2 ,

Solution provided based on the given scenario. It is always recommended to give right scenario for right output. However, if your dimention table is connected to the fact table, you should use allexcept(dim table, column1, column2) like this. This will work. Else, please share right scenario with sample data.

Re: Dax calculate total amount ignoring filter

NT2 — Thu, 28 Nov 2024 22:23:27 GMT

Yes based on the scenario I gave this did not work for me I tested all expect but did not gave me the correct result.

Thank you for your answer

Re: Dax calculate total amount ignoring filter

Anonymous — Fri, 29 Nov 2024 02:40:12 GMT

Thanks for the reply from Rupak_bi ,FreemanZ and VahidDM , please allow me to provide another insight:

Hi, NT2

Regarding your question, I have provided the following two solutions:

Firstly, create the following calculated table:

Table 2 = SUMMARIZE( 'Table', 'Table'[store], 'Table'[product], "amount", CALCULATE( SUM('Table'[amount]), FILTER( ALLSELECTED('Table'), 'Table'[store] = MAX('Table'[store]) ) ) )

The result is as follows:

Secondly, use the following measures:

Measure = VAR cc = SUMMARIZE( 'Table', 'Table'[store], 'Table'[product], "kk2", CALCULATE( SUM('Table'[amount]), FILTER( ALLSELECTED('Table'), 'Table'[store] = MAX('Table'[store]) ) ) ) VAR cc1 = IF( HASONEVALUE('Table'[store]), CALCULATE( SUM('Table'[amount]), FILTER( ALLSELECTED('Table'), 'Table'[store] = MAX('Table'[store]) ) ), SUMX(cc, [kk2]) ) RETURN cc1

The result is as follows:

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

Re: Dax calculate total amount ignoring filter

NT2 — Fri, 29 Nov 2024 09:07:42 GMT

Thank you a lot for your help, but as I mentioned the columns of product and store need to be used from a dimension table

Need to put in the visual column from store, product and the measure......

Re: Dax calculate total amount ignoring filter

Rupak_bi — Fri, 29 Nov 2024 13:51:40 GMT

Hi, NT2 ,

As per My understanding, this will not work if you use product and stroe from different table along with measure. However, My point is
1. If you already have Store and product in the fatc table, why **bleep** you use that in the matrix?
2. This can also be done in a calculated column same way and then can be referred in the matrix and that will wotk with stroe and product from dimentions. Why to use Measure??

Re: Dax calculate total amount ignoring filter

NT2 — Sat, 30 Nov 2024 00:32:27 GMT

Hello,

1 Because I have more than one fact table. That I will need to put it in the visual

2 if I will do it as a calculated column I will have a wrong total

Re: Dax calculate total amount ignoring filter

Rupak_bi — Sat, 30 Nov 2024 07:17:26 GMT

Hi NT2 ,

A calculated column along with a measure will work. Please see below

Let me know if this approach works. Thanks

Re: Dax calculate total amount ignoring filter

Dangar332 — Sun, 01 Dec 2024 17:27:34 GMT

HI, NT2

Try below approach
As we know in Microsoft Table and Matrix total are broken since long time so we need to work around to acheive desire result

Total Amount = sumx( summarizecolumn('store'[store column],'Product'[Product Column]), CALCULATE( SUM('FactTable'[amount]), REMOVEFILTERS('Product'[Product Column])) )

Please vote for Greg_Deckler 's Idea
Please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

Best Regards,
Dangar

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