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 measures show the cumulated turnover over the months of the specific year.

 

Now I tried to compare those 2 measures and what I want to see is the percentual change of the turnover from this year compared to last year cumulated per month:

 

Dev. vs. Cumul. 2016 = DIVIDE(TOTALYTD(SUM(RE2016[TO2016])|'Calendar'[Date])|(TOTALYTD(SUM(RE2017act[TO2017])|'Calendar'[Date])))

 

This DAX-Expression somehow doesn't show any values on the table chart on my Power BI Desktop. Any suggestions how to proceed on this one?

 

 

Need Help

10 Replies

  • Paddhof1984's avatar
    Paddhof1984
    Helper III
    9 years ago

    This DAX-expression for an additional measure also doesn't shows any values on the chart:

     

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

    • Paddhof1984's avatar
      Paddhof1984
      Helper III
      9 years ago

      v-huizhn-msft

       

      Maybe you got any clue how to implement this DAX-Expression, so the right values show up on the report?

  • 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

      • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hello,
    I have a somewhat similar issue:

     

    I’m required to divide the aggregated sum of two values in two different Periods (P6/ P12) and Scenarios (Actual/Budget). The issue is how to store the values and then utilise them in the final measure - Actual#MAP131_ARO while using only P6 in the Period Slicer or preferrably P6 and P12 in the Visual Filter

     

    1. A#MAP131_P6 =

    CALCULATE(

                SUM(GRA_extract[Value]),

                FILTER(GRA_extract, GRA_extract[Account]="MAP131"),

                FILTER (GRA_extract, GRA_extract[Currency]="GBP"),

                NOT(GRA_extract[ServiceLine]) IN {"AllCustom2","TotalUnit","AboveUnit", "TotalAbove", "TotalServices"},

                NOT(GRA_extract[Sector]) IN {"AllCustom1","TotalUnit","TotalSectors"},

                FILTER ( GRA_extract, GRA_extract[Custom3] = "IFRS100PC" ),

                FILTER ( GRA_extract, GRA_Extract[Period] = "P6" )

                  )

    Result = 12623440.522209276

     

    1. A#MAP131_P12 =

        CALCULATE(

                SUM(GRA_extract[Value]),

                FILTER(GRA_extract, GRA_extract[Account]="MAP131"),

                FILTER (GRA_extract, GRA_Extract[Scenario]="Budget"),

                NOT(GRA_extract[ServiceLine]) IN {"AllCustom2","TotalUnit","AboveUnit", "TotalAbove", "TotalServices"},

                NOT(GRA_extract[Sector]) IN {"AllCustom1","TotalUnit","TotalSectors"},

                FILTER ( GRA_extract, GRA_extract[Custom3] = "IFRS100PC" ),

                FILTER ( GRA_extract, GRA_Extract[Period] = "P12" )

                  )

     

    Result = 36209485.11878476

     

    Actual#MAP131_ARO = ([A#MAP131_P6]/[A#MAP131_P12])

     

    The final result is Infinity

     

    I'd appreciate your kind advice.