dax handling summary values in a matrix report

0
votes

Source data columns are Store, Product, StoreSales, and ProductSales

StoreSales has duplicate values, even across different Stores.

Looking for a dax measure to handle StoreSales as described in the image.

This is the closest so far, but doesn't account for duplicates between stores.

Store Sales:= sumx(DISTINCT(_Sales[StoreSales), _Sales[StoreSales])

screenshot of source and pivot table enter image description here

1
daxssas-tabular

1 Answers

1
votes

To answer your question directly, this formula should give you the desired result:

Desired Result for Store Sales = 
IF(ISFILTERED(_Sales[Store]), SUM(_Store[Store Sales]))

However, I recommend to revisit your data model design. It's conceptually incorrect (you are mixing detailed data with the summary of the same data), and you will have serious problems with DAX. A better way to structure your data:

  • Remove column "Store Sales". It's redundant and does not fit the data level of detail.
  • Rename column "Product Sales" into "Sale Amount". It's just sale amount, without any qualifiers.
  • Create a measure "Total Sales" = SUM(_Sales[Sale Amount]). It will correctly calculate total sales both on product and store levels.

If you need a special measure for store sales, use SUMX:

Store-level sales = SUMX (VALUES(_Sales[Store]), [Total Sales])

And if you need to show product contributions to store sales:

Product Contribution = `DIVIDE([Total Sales], [Store-Level Sales])