Forum Discussion

peter_rips's avatar
peter_rips
Frequent Visitor
9 years ago
Solved

Normalize monthly values based on 1st month SUM/total values

Hello there, I'm stuck at the moment, so I hope someone can give me some light at this stage regarding this subject. I've been trying to normalize my data, using the following example (extracted fr...
Need Help
  • peter_rips's avatar
    peter_rips
    9 years ago

    Hi v-chuncz-msft, thanks for coming back to me.

    It´s not easy since I have a lot of confidential data and several metrics.

     

    Anyway, hopefully, I managed to find a solution for this, the following way:

     

    1- 

    IndexR2 = 
CALCULATE (
        sum(Facts[Sales_U]), 
            CALCULATETABLE ( DATESBETWEEN('Date'[DateFullName],STARTOFMONTH('Date'[DateFullName]),ENDOFMONTH(STARTOFMONTH('Date'[DateFullName]))), ALLSELECTED ( 'Date' )
    )
)

     

    2-

    IndexR2N = DIVIDE ( Calculate(Sum(Facts[Sales_U])), [IndexR2] ) * 100

     

    and that's it.

     

    Thanks anyway, and sorry case I was not clear and able to share the .pbix

     

    Peter

v-chuncz-msft's avatar
v-chuncz-msft
Icon for Community Support rankCommunity Support
9 years ago

peter_rips,

 

The example above is not very clear. It would be better if you could share us your simplified .pbix file.

peter_rips's avatar
peter_rips
Frequent Visitor
9 years ago

Hi v-chuncz-msft, thanks for coming back to me.

It´s not easy since I have a lot of confidential data and several metrics.

 

Anyway, hopefully, I managed to find a solution for this, the following way:

 

1- 

IndexR2 = 
CALCULATE (
        sum(Facts[Sales_U]), 
        CALCULATETABLE ( DATESBETWEEN('Date'[DateFullName],STARTOFMONTH('Date'[DateFullName]),ENDOFMONTH(STARTOFMONTH('Date'[DateFullName]))), ALLSELECTED ( 'Date' )
    )
)

 

2-

IndexR2N = DIVIDE ( Calculate(Sum(Facts[Sales_U])), [IndexR2] ) * 100

 

and that's it.

 

Thanks anyway, and sorry case I was not clear and able to share the .pbix

 

Peter