Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Tough One: Margin Mix Variance and solving wrong totals

First, this is the second post. My first one disappeared. I recommend keeping a copy of your posts in a text file as this has happened before.   I want to automate the impact of product mix change ...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous

     

    You may refer to below measure: If it is not your case, please share your data sample file which could reproduce your scenario and your desired output, you can upload it to OneDrive or Dropbox and post the link here. Show a simplified sample as below:

    C =
CALCULATE ( SUM ( Table[Sales PY] ) )
    / CALCULATE ( SUM ( Table[Sales PY] ), ALL ( Table ) )
    E =
SUMX (
    SUMMARIZE ( Table, Table[Product] ),
    CALCULATE ( SUM ( Table[Margin%PY] ) ) * [D]
)
    F =
SUMX ( SUMMARIZE ( Table, Table[Product] ), [E] / [D] )

    Regards,
    Cherie

  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Cherie,

     

    We solved this together!

     

    I need ALLSELECTED in [ C ] “Product Revenue Percent of Total” as that will calculate correctly with filters applied.

     

    This will deliver correct rows and totals with filters applied. Your help is new measure E-TOTAL, thank you.

     

    This stream of measures can be consolidated and I most likely will do that.

     

    Cheers!

     

    A = Prior Year Revenue

    B = Prior Year Margin %

    C = [Current Year Revenue] / CALCULATE( [Current Year Revenue] ,ALLSELECTED())

    D = CALCULATE( [ A ], ALLSELECTED() ) * [ C ]

    E = [ D ] * [ B ]   (This is the Line Item Values, the column actually comes from the virtual table below)

     

    New Measure: This is your formula for the Total of the Margin Mix Column and comes out of the virtual table below:

     

    E-TOTAL = SUMX(

        SUMMARIZE( Table3, [Product] ) ,

        CALCULATE(  [B] ) * [D] )

     

     

    Virtual Table:

     

    MIX Margin PY at CY Mix  =

    VAR Margin_PY_at_CY = [ E ]

    VAR Margin_PY_TOTAL = CALCULATE( [ E-Total ] , FILTER( Table3 , [ Product ] <> BLANK() ) )

     

    RETURN

    IF( HASONEVALUE( Table3[Product] ) ,

    Margin_PY_at_CY ,

    Margin_PY_TOTAL )

     

    F = New Margin % = MIX Margin PY at CY Mix  / [ D ]

v-cherch-msft's avatar
v-cherch-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous

 

You may refer to below measure: If it is not your case, please share your data sample file which could reproduce your scenario and your desired output, you can upload it to OneDrive or Dropbox and post the link here. Show a simplified sample as below:

C =
CALCULATE ( SUM ( Table[Sales PY] ) )
    / CALCULATE ( SUM ( Table[Sales PY] ), ALL ( Table ) )
E =
SUMX (
    SUMMARIZE ( Table, Table[Product] ),
    CALCULATE ( SUM ( Table[Margin%PY] ) ) * [D]
)
F =
SUMX ( SUMMARIZE ( Table, Table[Product] ), [E] / [D] )

Regards,
Cherie

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Oh so close. 

     

    When I use these I get incorrect rows and correct total. That's the opposite oif my formuls which is correct at rows and incorrect total. 

     

    I'm trying to incorporate an IF( HASONEVALUE() to combine them but am failing. 

     

    For C = My formula incorporates ALLSELECTED(). I could not get ALL() to work. 

     

    I'll get back to you my friend with what I ended up with or I will create a data set if i fail.

     

    Cheers!

     

    • v-cherch-msft's avatar
      v-cherch-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      7 years ago

       

      Hi Anonymous

       

      I've tried to use ALLSELECTED() for C with my test data. Show a sample as below for you to check if it could help you.

       

      D =
      IF (
       HASONEVALUE ( Table3[Product] ),
       CALCULATE ( SUM ( Table3[Sales PY] ) ) * [C],
       SUMX (
       SUMMARIZE ( Table3, Table3[Product] ),
       CALCULATE ( SUM ( Table3[Sales PY] ) ) * [C]
       )
      )
      E =
IF (
    HASONEVALUE ( Table3[Product] ),
    CALCULATE ( SUM ( Table3[Margin%PY] ) ) * [D],
    SUMX (
        SUMMARIZE ( Table3, Table3[Product] ),
        CALCULATE ( SUM ( Table3[Margin%PY] ) ) * [D]
    )
)
      F = SUMX(SUMMARIZE(Table3,Table3[Product]),[E]/[D])

       

       

      Regards,

      Cherie

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Cherie,

         

        We solved this together!

         

        I need ALLSELECTED in [ C ] “Product Revenue Percent of Total” as that will calculate correctly with filters applied.

         

        This will deliver correct rows and totals with filters applied. Your help is new measure E-TOTAL, thank you.

         

        This stream of measures can be consolidated and I most likely will do that.

         

        Cheers!

         

        A = Prior Year Revenue

        B = Prior Year Margin %

        C = [Current Year Revenue] / CALCULATE( [Current Year Revenue] ,ALLSELECTED())

        D = CALCULATE( [ A ], ALLSELECTED() ) * [ C ]

        E = [ D ] * [ B ]   (This is the Line Item Values, the column actually comes from the virtual table below)

         

        New Measure: This is your formula for the Total of the Margin Mix Column and comes out of the virtual table below:

         

        E-TOTAL = SUMX(

            SUMMARIZE( Table3, [Product] ) ,

            CALCULATE(  [B] ) * [D] )

         

         

        Virtual Table:

         

        MIX Margin PY at CY Mix  =

        VAR Margin_PY_at_CY = [ E ]

        VAR Margin_PY_TOTAL = CALCULATE( [ E-Total ] , FILTER( Table3 , [ Product ] <> BLANK() ) )

         

        RETURN

        IF( HASONEVALUE( Table3[Product] ) ,

        Margin_PY_at_CY ,

        Margin_PY_TOTAL )

         

        F = New Margin % = MIX Margin PY at CY Mix  / [ D ]