Forum Discussion

NT2's avatar
NT2
Frequent Visitor
1 year ago
Solved

Dax calculate total amount ignoring filter

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 AProduct X5
Store AProduct Y8
Store BProduct Z3
Store AProduct X8
Store AProduct Y8
  • 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

  • Rupak_bi's avatar
    Rupak_bi
    1 year ago

    Hi NT2 ,

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

    Let me know if this approach works. Thanks

18 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    1 year ago

    hi NT2 ,

     

    try like:

    measure =

    CALCULATE(

    SUM(fact[amount]),

    ALL(fact),

    VALUES(fact[store])

    )

    • NT2's avatar
      NT2
      Frequent Visitor
      1 year ago

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

      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User
        1 year ago

        hi NT2 ,

         

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

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      NT2
      Frequent Visitor
      1 year ago

      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's avatar
        VahidDM
        Icon for Super User rankSuper User
        1 year ago

        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 

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    1 year ago

    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.