Forum Discussion

SianM's avatar
SianM
Frequent Visitor
8 years ago
Solved

Store measure output in new table

Hi,    I am generating a set of averages for a data set that I want to store and use as static inputs to different visualisations.    What is the best way for me to do this? Using calculated colu...
  • danextian's avatar
    danextian
    8 years ago

    Hi SianM

     

    Sorry. I was thinking that you were going to use a measure aggregate x measure (like sumx, countx, etc).  Summarize function actually accepts a measure as an argument.  So you could use

     

    Summarizetest =
SUMMARIZE ( Raw1, Raw1[Milestone Status], "Average", [Dynamic Range] )

    Alternatively, you can use GROUPBY() and CURRENTGROUP() functions.  Example:

     

    Summarizetest =
GROUPBY (
    Raw1,
    Raw1[Milestone Status],
    "Average", AVERAGEX ( CURRENTGROUP (), [Dynamic Range] )
)

    More about these two functions at https://www.sqlbi.com/articles/nested-grouping-using-groupby-vs-summarize/

danextian's avatar
danextian
Icon for Super User rankSuper User
8 years ago

Hi SianM,

 

You can use a combination of SUMMARIZE() and ADDCOLUMS() functions. Example:

 

=
ADDCOLUMNS (
    SUMMARIZE ( Table, Table[Column1], Table[Column2] ),
    "Column Name", [Measure]
)
SianM's avatar
SianM
Frequent Visitor
8 years ago

Hi danextian,

 

Thank you so much! I am getting there with my result, however at the moment I am getting the total for each "group by" in the summarize function. This is giving me the same value for each 'category'. The category being the field I am 'grouping by' in my summarize function. 

 

Current Result

 

Current Result

Do you know what the correct DAX is to make it calculate considering all of the categories? Anonymous maybe you can help me with summarize DAX?


Sorry I know this is probably very simple, I am very new to power BI. 

 

expected result

 

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    8 years ago

    Hi SianM

     

    Sorry. I was thinking that you were going to use a measure aggregate x measure (like sumx, countx, etc).  Summarize function actually accepts a measure as an argument.  So you could use

     

    Summarizetest =
SUMMARIZE ( Raw1, Raw1[Milestone Status], "Average", [Dynamic Range] )

    Alternatively, you can use GROUPBY() and CURRENTGROUP() functions.  Example:

     

    Summarizetest =
GROUPBY (
    Raw1,
    Raw1[Milestone Status],
    "Average", AVERAGEX ( CURRENTGROUP (), [Dynamic Range] )
)

    More about these two functions at https://www.sqlbi.com/articles/nested-grouping-using-groupby-vs-summarize/

    • SianM's avatar
      SianM
      Frequent Visitor
      8 years ago

      Hi danextian,

       

      Thank you for pointing me in the direction of Addcolumns & GroupBy! Turns out summarize was not what was needed. I have managed to obtain the result I was looking for using a combination of the above. 

       

      Thank you Anonymous for your help also, testing a few things really helped me understand more how both of these functions work. 

       

      This has been a huge relief to finally get working. Thank you again for taking the time to assist me. :smileyvery-happy:


      Sian