Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have the data in the below format.
Storenumber | FW | Period | CUST | Sales OP |
1 | FW2 | PRE | REP | 12356 |
1 | FW2 | PRE | NEW | 12356 |
1 | FW3 | PRE | REP | 20000 |
1 | FW3 | PRE | NEW | 20000 |
2 | FW2 | PRE | REP | 10000 |
2 | FW2 | PRE | NEW | 10000 |
2 | FW3 | PRE | REP | 80000 |
2 | FW3 | PRE | NEW | 80000 |
For every store, I have two types of customers for a week. Now, I need to get the total sales OP for PRE PERIOD without adding the duplicates.
For example, sales OP for PRE-PERIOD should be 123456 + 20000+10000+80000.
I have considered the below approach but was unsuccessful.
I need to get the maximum sales op/store/week and add all the maximum values to get the total sales op. I have used the below DAX, but was unsuccessful.
_wk_2_10_OP =
Salesnet
/
PS: There are chances that duplicate records can be present for the same customer type. Salesnet column is not present in sample data.
eg: The below record can be repeated twice, but 123456 should be added only once.
1 | FW2 | PRE | REP | 12356 |
Solved! Go to Solution.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a measure as below.
Result =
var tab =
GROUPBY(
'Table',
'Table'[Storenumber],
'Table'[FW],
'Table'[Period],
"Max",MAXX(CURRENTGROUP(),'Table'[Sales OP])
)
return
DIVIDE(
SUMX(
FILTER(
tab,
[Period]="PRE"
),
[Max]
),
SUMX(
FILTER(
tab,
[Period]="POST"
),
[Max]
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a measure as below.
Result =
var tab =
GROUPBY(
'Table',
'Table'[Storenumber],
'Table'[FW],
'Table'[Period],
"Max",MAXX(CURRENTGROUP(),'Table'[Sales OP])
)
return
DIVIDE(
SUMX(
FILTER(
tab,
[Period]="PRE"
),
[Max]
),
SUMX(
FILTER(
tab,
[Period]="POST"
),
[Max]
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please try this expression
Sum of Max Sales per Store per Week =
VAR __summarytable =
ADDCOLUMNS (
SUMMARIZE ( Table, Table[FW], Table[Storenumber] ),
"@maxsales", CALCULATE ( MAX ( Table[Sales OP] ) )
)
RETURN
SUMX ( __summarytable, [@maxsales] )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi Pat,
Thanks for the reply.
Can it be done without creating another column? I want to divide that sum(maximum values for all stores) by another value and display that value as Card(123). In the sample data, I have provided only PRE Period but I have got Post Period also.
Regards
Prakash
The expression I provided is for a measure so no extra column needed. If you want to compare your PRE and POST, you could adapt the measure like this, for example:
PRE vs POST Max Sales per Store per Week =
VAR __summarytable =
ADDCOLUMNS (
SUMMARIZE ( Table, Table[FW], Table[Storenumber], Table[Period] ),
"@maxsales", CALCULATE ( MAX ( Table[Sales OP] ) )
)
VAR __PRE =
SUMX ( FILTER ( __summarytable, Table[Period] = "PRE" ), [@maxsales] )
VAR __POST =
SUMX ( FILTER ( __summarytable, Table[Period] = "POST" ), [@maxsales] )
RETURN
DIVIDE ( __PRE, __POST )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.