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

      • Paddhof1984's avatar
        Paddhof1984
        Helper III
        9 years ago

        Anonymous

         

        Hello,

         

        somehow, when I add the the turnover from my tables to a splitted bar chart, the procentual change over the year differs to the one from my measuers ([GT 2016]-[GT 2017])/[GT 2016].

         

        Here's my sample file, maybe you can tell me, why there's a difference between the shown values and the values calculated by the measure:

         

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