Forum Discussion
DAX help needed
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi lbendlin ,
Thank you for your reply, I have shared sample data below and dax formula, really appreciate your help,
looking forward to hear your feedback.
id product product_id amount s_id
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
sales_ =
VAR delivery =
SUMMARIZE (
fact_sales,
fact_sales[id],
fact_sales[product],
"totalsales",
IF (
fact_sales[product_id] = "0",
CALCULATE (
SUM ( fact_sales[amount] ),
TOPN ( 4, fact_sales, fact_sales[s_id], DESC )
),
IF (
fact_sales[product_id] = "1",
CALCULATE (
SUM ( fact_sales[amount] ),
TOPN ( 2, fact_sales, fact_sales[s_id], DESC )
),
CALCULATE (
SUM ( fact_sales[amount] ),
TOPN ( 1, fact_sales, fact_sales[s_id], DESC )
)
)
)
)
RETURN
SUMX ( delivery, [totalsales] )
can you please re-post the sample data in usable format?
Hi lbendlin , I have tried but it looks messy again can you open the excel file below ?
https://drive.google.com/file/d/1w7YQPyczBTFPcszM8je4aAefhzNiarri/view?usp=drive_link
id product product_id amount s_id 100-4024449860 3 0 1,9 10668506 100-4024449860 3 0 1,9 10668868 100-4024449860 3 0 1,9 10670033 100-4023063296 2 0 0 199334 100-4023063321 2 0 2,7 1866829 100-4023063351 2 0 5,8 78233 100-4023063537 2 0 0,8 199336 100-4023063906 2 1 21 1889161 100-4023063997 2 1 1,4 1984698 100-4023064113 2 1 5,5 1992505 100-4023064143 2 1 0 77493 100-4023064155 2 1 10,9 10037981 100-4023064180 2 1 18,4 1994162 100-4023064180 2 1 2,7 2015940 100-4023064180 2 2 3,2 2034646 100-4023064287 2 2 1,3 10613124 100-4023064570 2 2 1,7 182786 100-4023064570 2 2 1,7 183063 100-4023064735 2 2 5,5 1994150 100-4023064946 2 2 4,6 10289201 100-4023065082 2 2 4,9 10038215 100-4023065082 2 2 1,7 10038252 100-4023065116 2 2 0,4 10037979 100-4023065148 2 2 0,7 199364 100-4023065287 2 2 0,6 199356 100-4023065356 2 2 2,2 10607848 100-4023065360 2 2 1,2 183745 100-4023065604 2 0 0,6 10036460 100-4023065850 2 1 0,6 34275 lbendlin , can you pls try this data?
Your sample data only has Product IDs 2 and 3 but the DAX explicitly calls out IDs 0 and 1. Please clarify.
How many rows are in your actual table?
This article is likely relevant for your issue: https://www.sqlbi.com/articles/all-the-secrets-of-summarize/