Forum Discussion

Paddhof1984's avatar
Paddhof1984
Helper III
9 years ago

Divide Measures

Hello dear PowerBI community,   I do need your help regarding a calculation i want to do with 2 created measures:   I do have 2 measures:   TO2016 cumul.    and   TO2017 cumul.   These 2 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Paddhof1984,

 

It will be help if you share some sample data and the measure formulas.

 

In addition, if your measure contains some specific filters or 'all' filter, they may not works in other measures.

Regards,

Xiaoxin Sheng

Paddhof1984's avatar
Paddhof1984
Helper III
9 years ago

Hello,

 

my measures:

 

TO2016 cumul.:

TO2016 cumul. = TOTALYTD(SUM(RE2016[TO2016])|'Calendar'[Date])

 

TO2017 cumul.:

TO2017 cumul. = TOTALYTD(SUM(RE2017act[TO2017act])|FILTER(ALL('Calendar'[Date])|'Calendar'[Date]<=MAX(RE2017act[PurchDate])))

 

sample Data:

https://1drv.ms/u/s!AgBF1Ha5yWKVj4g5FAec2PkvLL9yYw

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Paddhof1984,

     

    Based on test ,your 'To2016 cumul' measure seems not works on table visual.
    After I modify its formula, the divide measures will works.

    TO2016 cumul. = TOTALYTD(SUM(RE2016[TO2016]),FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=MAX(RE2016[PurchDate])))
TO2017 cumul. = TOTALYTD(SUM(RE2017act[TO2017act]),FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=MAX(RE2017act[PurchDate])))

     

     

    Regards,

    Xiaoxin Sheng

    • Paddhof1984's avatar
      Paddhof1984
      Helper III
      9 years ago

      Anonymous Thanks a lot, the measures now show up on my new measure:

       

      Dev. vs. Cumul. 2016 = DIVIDE('add calc'[TO2017 cumul.]-'add calc'[TO2016 cumul.]|'add calc'[TO2016 cumul.])

       

      Last question regarding this one: how can I intersect this measure to only to show the dates of RE2017act[month].

       

      If I add Intersect to Dev. vs. Cumul. 2016, the values shown on the chart are wrong or a error is shown.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi Paddhof1984,

         

        So you want to hide the blank records which not exists in 2017, right?

        If this is a case, you can try to use below formula:

         

        Dev. vs. Cumul= if(ISBLANK([TO2017 cumul.])=FALSE(), DIVIDE([TO2017 cumul.]-[TO2016 cumul.],[TO2016 cumul.],0))

         

         

        Regards,

        Xiaoxin Sheng