Forum Discussion

2366's avatar
2366
Icon for Helper I rankHelper I
6 years ago
Solved

DAX - Adding Measures by using another measure as input

Hi,

 

I have a calculated measure with formula  as measure 1 = sum(a)/sum(b) now I want to create another measure which will do = sum of all(measure1)

Dim  A         B     Measure1   Measure2

A      10        5           2                  11

B      100      25         4                  11

C      1000    20         5                  11 

 

Current behaviour of measure2 is sum of all A / sum of all B = 22.2, I do not want this. How can I calculate sum of measure1 across all rows?

 

Please help

 

Thanks in advance

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi 2366 ,

     

    We can try to use the following measure to meet your requirement:

     

    TotalMeasure =
SUMX (
    DISTINCT ( 'Table'[Year] ),
    CALCULATE (
        [First Measure]
    )
)

     

    It if does not meet your requirement, could you please share your 1st measure if it does not contain any confidential information?


    Best regards,

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    So, you want to create a table variable VAR and use ADDCOLUMNS, something like:

    Measure2 =
  VAR __Table =
    ADDCOLUMNS(
      'Table',
      "Measure1",[Measure1]
    )
RETURN
  SUMX(__Table,[Measure1])

     

    • 2366's avatar
      2366
      Icon for Helper I rankHelper I
      6 years ago

      Thanks but the new measure is now returning blanks.

       

      Aplogies I havent given the full background of what I am trying to achieve -

       

      I have a data set like this

      Year    Month 1             2            3             4                 

      1          100               300           600          1500

      2          200               400           800

      3          300               500

      4          400         

       

      I am calculating my 1st measure as sum(next months value)/sum(current months value) by raking the month column.

       

      so my result currently looks like this now -

       

      Year   M 1             M2        M3                        

      1          3               2           .4          

      2          2               2           

      3          .6              

       

      In the next step I want to display a single row as

      M1    M2    M3

      5.6     4        .4

       

      Hope this helps?

      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi 2366 ,

         

        We can try to use the following measure to meet your requirement:

         

        TotalMeasure =
SUMX (
    DISTINCT ( 'Table'[Year] ),
    CALCULATE (
        [First Measure]
    )
)

         

        It if does not meet your requirement, could you please share your 1st measure if it does not contain any confidential information?


        Best regards,