ALL not working properly with slicer

[Min_02]

Hi everyone,

I have a measure: Total sales = calculate(SUM(Sales_1[Sales]), ALL(Sales_1[Store Name]))

Then, I have a table where I have "Brands" on rows, and the created measure "Total Sales" and "Sales" on columns. The created measure is working as expected, but then, I have a slicer with "Store Name", to see only the values for that store. However, when I select a store on the slicer, the created value that should show the total value independently on the selected store on slicer, is not working properly, as for some stores, it shows a different value.

Any idea on how to correct this so even selecting a value on the slicer, the value showed is the same?

Thanks a lot

[Icey]

Hi @Min_02 ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,
Icey

[Icey · Accepted solution]

Hi @Min_02 ,

How about this?

1. Create a new SlicerTable.

SlicerTable = VALUES ( Sales_1[Store Name] )

2. Change your measure like so:

Total sales =
VAR SelectedStore =
    SELECTEDVALUE ( 'SlicerTable'[Store Name] )
RETURN
    IF (
        SelectedStore = BLANK (),
        CALCULATE ( SUM ( Sales_1[Sales] ), ALL ( Sales_1[Store Name] ) ),
        CALCULATE ( SUM ( Sales_1[Sales] ), Sales_1[Store Name] ) = SelectedStore
    )

3. Use 'SlicerTable'[Store Name] as a Slicer.

Best Regards,

Icey

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

[amitchandak]

@Min_02 , move store to a separate dimension and try

Total sales = calculate(SUM(Sales_1[Sales]), ALL(Store [Store Name]))

[Tahreem24]

@Min_02 , Use the ALLEXCEPT instead of ALL

Total sales = calculate(SUM(Sales_1[Sales]), ALLEXCEPT(Sales_1,Sales_1[Store Name]))

[Min_02]

Hi @Tahreem24 , 

I'm afraid this doesn't work...

Any other idea?

Thanks

[Mariusz]

Hi @Min_02 

Use ALLSELECTED instead of ALL

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Min_02]

Hi,

If I use ALLSELECTED, the measure is working properly if I don't use the "Store name" slicer. However, when I use it, the value that appears is exactly "Sales", not total sales... 

Any other idea?

Thanks