Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a sales table with sales on different aggregated levels. A simplification is like this:
Market | Sales |
Total Market | 100 |
Customer A | 75 |
Banner A1 | 20 |
Banner A2 | 30 |
Banner A3 | 25 |
Customer B | 25 |
Banner B1 | 10 |
Banner B2 | 5 |
Banner B3 | 5 |
So the table holds both Total sales (per sku, per period ect) and sales per customer and per banner (sub customer). When summing banners for Customer B (Banner B1 to B3) they do not sum up to total Customer B - there is a residual. So - I want to calculate the residual and would like to have it on the same table as a "Banner B4 (other)"
Market | Sales |
Total Market | 100 |
Customer A | 75 |
Banner A1 | 20 |
Banner A2 | 30 |
Banner A3 | 25 |
Customer B | 25 |
Banner B1 | 10 |
Banner B2 | 5 |
Banner B3 | 5 |
Banner B4 (other) | 5 |
But I have no idea how to approach that - any guidance in the right direction would be appreciated...!
Br Kent
Solved! Go to Solution.
@Anonymous
Please see attached file as well
@Anonymous
Try this calculated Table
From the Modelling Tab>>New Table
Calculated Table = VAR AddColumn = ADDCOLUMNS ( Table1, "Missing Sales", VAR myCustomer = IF ( SEARCH ( "Customer", [Market], 1, 0 ) > 0, RIGHT ( [Market], 1 ) ) VAR mysales = CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, SEARCH ( "Banner " & myCustomer, Table1[Market], 1, 0 ) > 0 ) ) VAR MaxBanner = CALCULATE ( MAX ( Table1[Market] ), FILTER ( Table1, SEARCH ( "Banner " & myCustomer, Table1[Market], 1, 0 ) > 0 ) ) RETURN IF ( SEARCH ( "Customer", [Market], 1, 0 ) > 0 && ( [Sales] - mysales ) > 0, "Banner " & myCustomer & RIGHT ( MaxBanner, 1 ) + 1 & " (other)" & "|" & [Sales] - mysales ) ) VAR desiredrows = FILTER ( SELECTCOLUMNS ( AddColumn, "Market", PATHITEM ( [Missing Sales], 1 ), "Sales", PATHITEM ( [Missing Sales], 2, 1 ) ), [Sales] > 0 ) RETURN UNION ( desiredrows, Table1 )
@Anonymous
Please see attached file as well
PERFECT - Thank You very much!
User | Count |
---|---|
102 | |
84 | |
77 | |
70 | |
67 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |