Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
I have a table with sales amount for different stores along with the storenumber from where the sale originated.
Country | Store | Store original | Amount |
A | S1 | O1 | 100 |
A | S1 | 100 | |
A | S2 | O1 | 100 |
A | S2 | 50 | |
A | S2 | O1 | 50 |
A | O1 | 100 | |
Total | 500 | ||
B | S1 | O2 | 100 |
B | S1 | 100 | |
B | S2 | O2 | 100 |
B | S2 | O2 | 50 |
B | O2 | 100 | |
Total | 450 |
I need to create a measure to calculate the sales for each corresponding store and the output needs to be like below,
Country | Store | total Sales |
A | S1 | 100 |
A | S2 | 50 |
A | O1 | 350 |
Total | 500 | |
B | S1 | 100 |
B | S2 | |
B | O2 | 350 |
Total | 450 |
Hi, @pbi_new_user
You can try the following methods.
Column:
New Store Column = IF([Store original]<>BLANK(),[Store original],[Store])
Result:
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the quick reply @v-zhangti .
This will work, but I cannot create a calculated column as I connect to a tabular model. Also I need to also calcualte the original sales data along with the corrected sales like below,
Country | Store | Sales Amount | Corrected Sales |
A | S1 | 200 | 100 |
A | S2 | 200 | 50 |
A | O1 | 100 | 350 |
B | S1 | 200 | 100 |
B | S2 | 150 | |
B | O2 | 100 | 350 |
So I need to write this as a measure.
Hi, @pbi_new_user
You can try the following methods.
Measure:
Sales Amount = SUM('Table'[Amount])
Measure 1 =
Var _table=FILTER(SUMMARIZE('Table','Table'[Country],'Table'[Store original],"Sum",SUM('Table'[Amount])),[Store original]<>BLANK())
Return
SUMX(_table,[Sum])
Measure 2 = SUMX(FILTER(ALL('Table'),[Country]=MAX('Table'[Country])),[Measure 1])
Measure 3 = MAXX(FILTER(ALL('Table'),[Country]=MAX('Table'[Country])),[Store original])
Corrected Sales =
Var _N1=CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),[Store original]=BLANK()&&[Country]=MAX('Table'[Country])&&[Store]=MAX('Table'[Store])))
Return
IF(MAX('Table'[Store])=[Measure 3],[Sales Amount]+[Measure 2],_N1)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-zhangti ,
I managed to replicate these measures in my data model but only issue is that I want to also see the totals on the country level which is wrong or not showing up. Please help.
My model:
Result in matrix visual:
Hi, @pbi_new_user
You can try the following methods.
Measure:
Result = Var _table=SUMMARIZE('Table','Table'[Country],'Table'[Store],"Sum",[Corrected Sales])
Return
IF(HASONEVALUE('Table'[Store]),[Corrected Sales],SUMX(_table,[Sum]))
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the help @v-zhangti .
I solved this by creating one measure instead of so many measures and my solution is much cleaner.
Correct Sales =
sum(Sales[Amount])
+ SUMX (
FILTER (
CALCULATETABLE ( 'Sales', REMOVEFILTERS ( 'Store' ) ),
CONTAINS ( 'Store', [Store], 'Sales'[Store Original] )
),
[Amount]
)
- SUMX (
FILTER ( 'Sales', 'Sales'[Store Original] <> BLANK() ),
[Amount]
)
Thanks for the solution @v-zhangti .
My data is really huge and sales table only has the store and store_original columns. The country column exists in a seperate store dimension. I am not able to replicate these measures there.
Hi @v-zhangti ,
To simplify the above scenario, this is how my sales table looks like,
And I want the output as below,
Store | Sales Amount |
S1 | 20 |
S2 | 30 |
S3 | 40 |
S4 | 50 |
O1 | 80 |
O2 | 60 |
Many thanks for your help.
User | Count |
---|---|
20 | |
14 | |
11 | |
8 | |
6 |
User | Count |
---|---|
23 | |
23 | |
20 | |
15 | |
10 |