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 Year]),[Gross Adds])

 

 

 

The result gives me an overall % change for the month compared to last year as -.11%.  I wanting to multiply the -.11% to PYSalesByWeek measure.  However [Month Change] will compute by row instead of overall month.  

 

% Applied = [PYSalesByWeek]+([MonthChange]*[PYSalesByWeek])

 

I'm wanting to remain at -.11.  instead of -.08 and -.1.32, respectively.    How to accomplish?  

 

PYSalesByWeekWeek Ending DateGross AddsMonthChange% Applied
294810/27/2024 0:002722-0.082716.23
291111/3/2024 0:00749-1.32-921.10

 

Applying Calculated Result to Rows

7 Replies

  • Irwan's avatar
    Irwan
    Icon for Super User rankSuper User
    1 year ago

    hello BrianNeedsHelp 

     

    i think you need to add ALL('table') in your measure so the result will calculate all data.

     

    Thank you.

  • 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.