DAX calculating stores with a sale of product selected

[dpombal]

Hi all,
I am facing an issue calculating following measure.

See attached PBIX report here https://1drv.ms/u/s!Am7buNMZi-gwgeBR4kHxdyh86pNB2w?e=YprTvr

I need to get the total amount of sales for stores with at least one sale (1 row) of selected product.

For each product, I need to filter the stores on which has been sold. And with the list of stores calculate the total amount of all sales in this store.

Product table is

Store table is

Fact Sales  Table

Data model

I need for selected product Iphone XR to get all sales for the stores where it has sales.

Selected Iphone XR is sold in Store 1,2,3 --> Get total amount of sales (7.600) in Stores 1,2,3 of ALL products not only iphone XR.

Steps

1.  Get list of stores with at least a single sale of product_name selected.

2. With the list of stores of step 1 , get total amount of sales for all products sold in this store.

[jdbuchanan71]

@dpombal 

You were almost there with your [Sum Value without product filter], you just need to apply a filter to the visual of [Sum Value] is not blank.

Is that what you were looking for?

[dpombal]

Thanks in advance , but your approach is not Not exactly I need this in a DAX measure and for each product  I need calculation as I explain below.

Custom visual filters are not valid for me because I need to use this in another measure.

See total sales by Apple Stores

Imagine my final requirement is a table with product_name and a measure dax with this amount

IPHONE_XR --> Iphone XR has at least 1 sale in Apple Store 1,2,3 (7600€)

MACBOOK_PRO--> Has at least one sale in App Store 2,4 (6800€)

[jdbuchanan71]

Hello @dpombal 

I believe this measure will achieve what you are looking for but let me know if that is not the case.

All Sales = 
VAR _Stores = FILTER ( VALUES ( STORE[STORE_NAME] ), [Sum Value] <> 0 )
RETURN
CALCULATE(
    [Sum Value],
    ALL ( 'PRODUCT'[PRODUCT_NAME] ),
    _Stores
)

[dpombal]

Hi, It is not exactly but we are very close to get the solution.

Here is updated report https://1drv.ms/u/s!Am7buNMZi-gwgeBT-E1buTUzIVxyyA?e=3rTt5T link with your measure

I need your measure working in the row of a product, not in totals. I need the amount in totals  for the product_name row.

I need a table with measure on a row context for each product_name .

PRODUCT_NAME  All Sales

Iphone Xr                7600 €

Macbook Pro 14     6800€

[jdbuchanan71 · Accepted solution]

@dpombal 

My measure works if you take the product name from the produt table.  You should really only have product name in one table and always pull it from there.  Even in your sample file you pulled product name from the product table in the left matrix and from the sales table in the right matrix.  If you want to show it in both tables and pull from either you would just add the second product name field to the ALL:

All Sales = 
VAR _Stores = FILTER ( VALUES ( STORE[STORE_NAME] ), [Sum Value] <> 0 )
RETURN
CALCULATE(
    [Sum Value],
    ALL ( 'PRODUCT'[PRODUCT_NAME] ),
    ALL ( SALES[PRODUCT_NAME] ),
    _Stores

[dpombal]

Totally correct many thanks!! @jdbuchanan71