cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pdarshik42 Frequent Visitor
Frequent Visitor

Issue with Customizing Sub Total per level wise

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

    )

1 REPLY 1
Community Support Team
Community Support Team

Re: Issue with Customizing Sub Total per level wise

@pdarshik42 

 

You may check if the following post helps.

https://community.powerbi.com/t5/Desktop/Sum-of-values-in-a-measure-with-divide-measure/m-p/296768#M...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 3,102 guests
Please welcome our newest community members: