Forum Discussion

BrianNeedsHelp's avatar
BrianNeedsHelp
Icon for Resolver I rankResolver I
1 year ago

Apply Month Calculation to Row Without Changing Month Calculation

I have this measure:         MonthChange = sumx(VALUES('Calendar'[Month Year]),[Gross Adds]- CALCULATE([Gross Adds],SAMEPERIODLASTYEAR('Calendar'[Calendar Date])))/sumx(VALUES('Calendar'[Month Y...
Applying Calculated Result to Rows
sanalytics's avatar
sanalytics
Icon for Super User rankSuper User
1 year ago

Hello BrianNeedsHelp 
Can you modify the % Applied measure with below code.

% Applied = 
VAR OverallMonthChange = CALCULATE([MonthChange], REMOVEFILTERS('Calendar'[Calendar Date]))
RETURN [PYSalesByWeek] + (OverallMonthChange * [PYSalesByWeek])


Regards

sanalytics

If it is your solution then please like and accept it as solution

BrianNeedsHelp's avatar
BrianNeedsHelp
Icon for Resolver I rankResolver I
1 year ago

Hi Irwan It looks like it would work, but it will always apply the logic to the row instead of the overall month.  For now I just put in a parameter and adjust the % manually based on what is in the overall month change Card.   Thx for replying-if you figure out another way please let me know.  

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi BrianNeedsHelp ， hello sanalytics and Irwan , thank you for your prompt reply!

    Please try this:

    % Applied = 
VAR OverallMonthChange = 
    CALCULATE(
        SUMX(VALUES('Calendar'[Month Year]), [Gross Adds] - CALCULATE([Gross Adds], SAMEPERIODLASTYEAR('Calendar'[Calendar Date]))) 
        / SUMX(VALUES('Calendar'[Month Year]), [Gross Adds]),
        ALL('Calendar'[Calendar Date])  
    )
RETURN 
[PYSalesByWeek] + (OverallMonthChange * [PYSalesByWeek])

     

    Best regards,

    Joyce

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • BrianNeedsHelp's avatar
      BrianNeedsHelp
      Icon for Resolver I rankResolver I
      1 year ago

      Perhaps I can explain it better with the table:  The formula you have will return different % based on the row.  Let's exclude the calculation you have in the Return and just Return Overall Month Change like I have below.  

      PYSalesByWeekWeek Ending DateGross Adds% Applied (Return of "Overall Month")
      453010/6/2024 0:002973-0.09
      367310/13/2024 0:0030130.05
      291710/20/2024 0:002931-0.01
      294810/27/2024 0:002722-0.08
      291111/3/2024 0:0017530.01

       

      Do you see how that equates to different %'s in each row?  That's what I'm trying to avoid.  I was trying to get the overall Month Change which is like -2% and multiply -2% * each row.  All of the formulas tried result in the exact same -they apply logic to the row.  Make sense?   

    • BrianNeedsHelp's avatar
      BrianNeedsHelp
      Icon for Resolver I rankResolver I
      1 year ago

      Anonymous sanalytics Irwan   I finally got this working.  To get the sum of the column so that it shows up in each row it's simply 

       

      AllFilter1= Calculate([Gross Adds],AllSELECTED());
All PY=Calculate([PYSALESBYWEEK],AllSELECTED());

       

      Then you can do a calculation by row to calculate the overall month change against the previous year(PY) such as: 

       

      Forecast1 = [PYSalesByWeek]+(([All Filter1]-[All PY])/[All Filter1])*[PYSalesByWeek]

       

      PYSalesByWeek  Week Ending Date  Gross Adds  All Filter1  All PY  Forecast1  
      291111/3/2024 0:00202710800131552276
      424211/10/2024 0:00307510800131553317
      294211/17/2024 0:00257710800131552300
      306011/24/2024 0:00312110800131552393


      I would appreciate kudos and an acceptance as solution please.  

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi BrianNeedsHelp ,

         

        Congratulations on solving this issue and thanks for sharing your solution.

         

        Please remember to accept your solution as answer.

         

        It will do great help to those who meet the similar question in this forum.

         

        Thanks again for your contribution.