Forum Discussion

Maxemus2000's avatar
Maxemus2000
Icon for Helper II rankHelper II
5 years ago
Solved

Need help with total sum

Hi Community,   My "sample" project has 3 tables; Sales, Item, and Store. Sales is a fact table and the other two are dimension. Both Item and Store have 1:* relationship with Sales table. Impact ...
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago
    You may have to use IN VALUES(Store[Store])

    Essentially, you're wanting to clear the filter on Store, but only for the selected stores, so define
    Impact =
    VAR _SelectedStores = VALUES(Store[Store])
    RETURN CALCULATE( SUM(Sales[Sales]), ALL(Store[Store]), Store[Store] IN _SelectedStores )

    Something like that should hopefully work.
  • Maxemus2000's avatar
    Maxemus2000
    5 years ago

    AllisonKennedy  Thanks again for the idea.

    I came up with a workaround solution. I added another Item table to use as slicer instead of using the original one and forced relationship between 2 tables from 1:1 to 1:Many with single direction. So, the slice would filter the original table. 

    I updated my measure to get all items from slicer table and use it to filter original item table. 

    AllStoreVol_2020 = 
VAR Items = VALUES('Item (2)'[Item])
RETURN
CALCULATE(SUM(Sales[Amount]),
    'Calendar'[Year] = 2020,
    'ID'[ID] = 1,
    ALL('Item'),
    'Item'[Item] IN Items)

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
5 years ago
Drilldown essentially changes the selected values, so alters what the ALLSELECTED total is calculating.

Can you share your sample file via OneDrive link please so I can do a few tests?
Maxemus2000's avatar
Maxemus2000
Icon for Helper II rankHelper II
5 years ago

AllisonKennedy  Thanks again for the idea.

I came up with a workaround solution. I added another Item table to use as slicer instead of using the original one and forced relationship between 2 tables from 1:1 to 1:Many with single direction. So, the slice would filter the original table. 

I updated my measure to get all items from slicer table and use it to filter original item table. 

AllStoreVol_2020 = 
VAR Items = VALUES('Item (2)'[Item])
RETURN
CALCULATE(SUM(Sales[Amount]),
    'Calendar'[Year] = 2020,
    'ID'[ID] = 1,
    ALL('Item'),
    'Item'[Item] IN Items)