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.
We are getting wrong total in Month Projection column while combining two different projection category which are in a single Customer Category Group.
Fact
Customer Category | FCST$ | FCST adjusted | day's weight | Shipment $ | Shipment LB | Month Projection | Customer Name | Projection Category | MTD_Weights | Current_Months_Weights |
Level 4 Category | 1800 | 1660 | 0.8325 | 1000 | 430 | 1610.75 | Sub-Total US region |
| 18.7 | 22.7 |
Level 3 Category | 1800 | 1660 | 0.8325 | 1000 | 430 | 1610.75 | Total Platinum customers |
| 18.7 | 22.7 |
Level 2 Category | 1800 | 1660 | 0.8325 | 1000 | 430 | 1610.75 | Total Platinum customers |
| 18.7 | 22.7 |
Level 1 Category | 600 | 550 | 0.8325 | 300 | 100 | 576.375 | ABC | 3 | 18.7 | 22.7 |
Level 1 Category | 800 | 720 | 0.8325 | 500 | 250 | 741.2 | DEF | 3 | 18.7 | 22.7 |
Level 1 Category | 400 | 390 | 0.8325 | 200 | 80 | 267 | GHI | 2 | 18.7 | 22.7 |
Grand Total | 1800 | 1660 | 0.8325 | 1000 | 430 | 1610.75 |
|
|
|
|
DimCust
Level 4 | Level 3 | Level 2 | Level 1 | Projection Category | Formula for each projection category |
Sub-Total US region | Total Platinum customers | Total Platinum customers | ABC | 3 | Shipment $ + ((1 - Day's weight)* (Shipment $ / Shipment LB) * FCST adjusted) |
Sub-Total US region | Total Platinum customers | Total Platinum customers | DEF | 3 | Shipment $ + ((1 - Day's weight)* (Shipment $ / Shipment LB) * FCST adjusted) |
Sub-Total US region | Total Platinum customers | Total Platinum customers | GHI | 2 | FCST $ + Shipment $ - (FCST $ * Day's weight) |
Month Projection = VAR IntRS = IF ( SELECTEDVALUE ( DimCust[ProjectionCategory] ) = "1", ( DIVIDE ( SUM ( 'Fact MTD'[Shipment $] ), MAX ( 'Fact MTD'[MTD_Weights] ) ) * MAX ( 'Fact MTD'[Current_Months_Weights] ) ), CALCULATE ( ( DIVIDE ( SUMX ( 'Fact MTD', 'Fact MTD'[Shipment $] ), MAXX ( 'Fact MTD', 'Fact MTD'[MTD_Weights] ) ) * MAXX ( 'Fact MTD', 'Fact MTD'[Current_Months_Weights] ) ), CONTAINSROW ( { "1" }, DimCust[ProjectionCategory] ) ) ) VAR Amz = IF ( SELECTEDVALUE ( DimCust[ProjectionCategory] ) = "2", SUM ( 'Fact MTD'[FCST $] ) + SUM ( 'Fact MTD'[Shipment $] ) - ( SUM ( 'Fact MTD'[FCST $] ) * MAX ( 'Fact MTD'[FactWight] ) ), CALCULATE ( SUMX ( 'Fact MTD', 'Fact MTD'[FCST $] ) + SUMX ( 'Fact MTD', 'Fact MTD'[Shipment $] ) - ( SUMX ( 'Fact MTD', 'Fact MTD'[FCST $] ) * MAXX ( 'Fact MTD', 'Fact MTD'[FactWight] ) ), CONTAINSROW ( { "2" }, DimCust[ProjectionCategory] ) ) ) VAR WalK = IF ( SELECTEDVALUE ( DimCust[ProjectionCategory] ) = "3", SUM ( 'Fact MTD'[Shipment $] ) + ( ( 1 - MAX ( 'Fact MTD'[FactWight] ) ) * ( DIVIDE ( SUM ( 'Fact MTD'[Shipment $] ), SUM ( 'Fact MTD'[Shipment LB] ) ) * SUM ( 'Fact MTD'[FCST adjusted] ) ) ), CALCULATE ( SUMX ( 'Fact MTD', 'Fact MTD'[Shipment $] ) + ( ( 1 - MAXX ( 'Fact MTD', 'Fact MTD'[FactWight] ) ) * ( DIVIDE ( SUMX ( 'Fact MTD', 'Fact MTD'[Shipment $] ), SUMX ( 'Fact MTD', 'Fact MTD'[Shipment LB] ) ) * SUMX ( 'Fact MTD', 'Fact MTD'[FCST adjusted] ) ) ), CONTAINSROW ( { "3" }, DimCust[ProjectionCategory] ) ) ) VAR NonV = IF ( SELECTEDVALUE ( DimCust[ProjectionCategory] ) = "99999", 0, 0 ) RETURN IF ( ISINSCOPE ( DimCust[Level 4] ) || ISINSCOPE ( DimCust[Level 3] ) || ISINSCOPE ( DimCust[Level 2] ) || ISINSCOPE ( DimCust[Level 1] ), WalK + IntRS + Amz, 0 ) |
@Anonymous
You may check if the following post helps.
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.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |