Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
YearValue | MonthValue | CustomerNo | Qty |
2019 | 1 | 1 | 10 |
2019 | 2 | 1 | 5 |
2019 | 3 | 2 | 20 |
2020 | 1 | 3 | 20 |
2020 | 2 | 2 | 5 |
2020 | 3 | 1 | 2 |
2020 | 1 | 1 | 10 |
We want to group by customer, which provide total as below; if the customer dont have value in previous year for a particualr moth or date and ignore in total and default the value to zero, Measured is preffered choice.
Expected Output
CustomerNo | YearValue | TotalQty |
1 | 2020 | 12 |
2 | 2020 | 5 |
3 | 2020 | 0 |
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @sneevand ,
You can use the following measure for instead:
Measure =
SUMX (
SUMMARIZE (
'Table',
'Table'[CustomerNo],
'Table'[YearValue],
"Total Qty",
IF (
CALCULATE (
SUM ( 'Table'[Qty] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[CustomerNo] ),
'Table'[YearValue]
= MAX ( 'Table'[YearValue] ) - 1
)
) = 0,
0,
CALCULATE (
SUM ( 'Table'[Qty] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[CustomerNo] ),
'Table'[YearValue] = MAX ( 'Table'[YearValue] )
)
)
)
),
[Total Qty]
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @sneevand ,
You can use the following measure:
Measure =
IF (
CALCULATE (
SUM ( 'Table'[Qty] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[CustomerNo] ),
'Table'[YearValue]
= MAX ( 'Table'[YearValue] ) - 1
)
) = 0,
0,
CALCULATE (
SUM ( 'Table'[Qty] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[CustomerNo] ),
'Table'[YearValue] = MAX ( 'Table'[YearValue] )
)
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Check the total instead of 17 it shows as 37 ; and it does not work simirally if i join with date dimension; my date dimension dates from min and max date of the customer table.
i updated the code to use dates from date dimension,
and did sumx(values(customertable),measure) , but total wont match.
Hi @sneevand ,
You can use the following measure for instead:
Measure =
SUMX (
SUMMARIZE (
'Table',
'Table'[CustomerNo],
'Table'[YearValue],
"Total Qty",
IF (
CALCULATE (
SUM ( 'Table'[Qty] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[CustomerNo] ),
'Table'[YearValue]
= MAX ( 'Table'[YearValue] ) - 1
)
) = 0,
0,
CALCULATE (
SUM ( 'Table'[Qty] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[CustomerNo] ),
'Table'[YearValue] = MAX ( 'Table'[YearValue] )
)
)
)
),
[Total Qty]
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @sneevand
Sorry I'm not following the logic for your expected output. You say if the customer dont have value in previous year for a particualr moth or date and ignore in total and default the value to zero
So with Customer 1 in 2020 they have values for Jan and Mar,but in 2019 they only have a value for Jan, so why is the 2020 total 12? Shouldn't the Mar 2020 value be treated as 0?
Same thing for Customer 2?
Regards
Phil
Proud to be a Super User!
corrected the data, Customer 3 dont have value for 2019 so it marked as 0 but customer 1,2 are having values in 2019 so need to do roll up for that year selected in filter like 2020, suppose i want 2019 then i will verify with 2018 data and get totals and so on..
User | Count |
---|---|
93 | |
86 | |
68 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |