dax handling summary values in a matrix report
up vote
0
down vote
favorite
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
dax ssas-tabular
edited Nov 11 at 21:54
RADO
2,6732614
asked Nov 11 at 17:42
user5050939
32
add a comment |
up vote
0
down vote
favorite
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
dax ssas-tabular
edited Nov 11 at 21:54
RADO
2,6732614
asked Nov 11 at 17:42
user5050939
32
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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
dax ssas-tabular
edited Nov 11 at 21:54
RADO
2,6732614
asked Nov 11 at 17:42
user5050939
32
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
dax ssas-tabular
dax ssas-tabular
edited Nov 11 at 21:54
RADO
2,6732614
asked Nov 11 at 17:42
user5050939
32
edited Nov 11 at 21:54
RADO
2,6732614
asked Nov 11 at 17:42
user5050939
32
edited Nov 11 at 21:54
RADO
2,6732614
edited Nov 11 at 21:54
RADO
2,6732614
edited Nov 11 at 21:54
RADO
2,6732614
2,6732614
asked Nov 11 at 17:42
user5050939
32
asked Nov 11 at 17:42
user5050939
32
asked Nov 11 at 17:42
user5050939
32
32
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
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])
answered Nov 11 at 22:25
RADO
2,6732614
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
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])
answered Nov 11 at 22:25
RADO
2,6732614
add a comment |
up vote
1
down vote
accepted
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])
answered Nov 11 at 22:25
RADO
2,6732614
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
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])
answered Nov 11 at 22:25
RADO
2,6732614
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])
answered Nov 11 at 22:25
RADO
2,6732614
answered Nov 11 at 22:25
RADO
2,6732614
answered Nov 11 at 22:25
RADO
2,6732614
answered Nov 11 at 22:25
RADO
2,6732614
2,6732614
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53251456%2fdax-handling-summary-values-in-a-matrix-report%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
