Forum Discussion
Store measure output in new table
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/
Hi Anonymous,
Thank you for your help. I think the SUMMARIZE function will only allow me to use existing columns to create the new table?
I need to use calculated measures. I cannot store the output of these measures in the columns because I need so many that it ends up creating a circular reference warning in power BI as they all use report date as a common denominator.
I am trying to store an average calculated for a whole portfolio cut different wants so I can then use them in my charts (screenshots below).
In the image below, I have brought in data from a pivot table in excel, and also have calculated in PowerBI using measures (to check they are the same). I don't want to have to create all of the portfolio averages in pivot tables in excel and have to refresh these separately to power BI.
The portfolio average is being caluclated as a measure, which I want to be used for two purposes in charts (see screenshot below)
1. stored for the total portfolio (all of the reports) in a different table
2. usable as a dynamic calculation in the charts as well that responds to filers.
Is calculated as
- the sum of a count of rows (based on a filter selected)
- the total number of reports included. For the total portfolio, this will be all of the reports as the distinct count of report dates in the data set.
DynamicAverage = SUM(Raw[CountMan])/[TotalReports]
These are the outputs I need stored as data
The image below provides an example of the chart I am trying to build. Baically, the red line shows the portfolio average (calcualted and stored as per above). I want this to stay constant as I maniuplate other filters around the chart. Therfore I need to store the portfolio average total as a data set for us in the charts. I need to do this for a number of charts with different aspects of portfolio average calculated.
- danextian
Super User
Hi SianM,
You can use a combination of SUMMARIZE() and ADDCOLUMS() functions. Example:
= ADDCOLUMNS ( SUMMARIZE ( Table, Table[Column1], Table[Column2] ), "Column Name", [Measure] )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
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/