Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years 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
2 years ago

Hello Anonymous 
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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years 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
      2 years ago

      Hi Anonymous ， 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years 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?