Dax calculate total amount ignoring filter

[NT2]

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

[Dangar332]

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.

[VahidDM]

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 

[NT2]

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

[VahidDM]

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 

[NT2]

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. 

[Rupak_bi]

hi @NT2 ,

Are you searching for a result like this??

if yes, than accept as solution.

[NT2]

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. 

[Rupak_bi]

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.

[NT2]

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

[v-linyulu-msft]

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.

[NT2]

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...... 

[Rupak_bi]

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??

[NT2]

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 

[Rupak_bi]

Hi @NT2 ,

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

Let me know if this approach works. Thanks

[FreemanZ]

hi @NT2 ,

try like:

measure =

CALCULATE(

SUM(fact[amount]),

ALL(fact),

VALUES(fact[store])

)

[NT2]

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

[FreemanZ]

hi @NT2 ,

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

[NT2]

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.