Store measure output in new table

[SianM]

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 columns was becoming tedious because I need a number of them, but also it started triggering circular reference warnings as I generated a number using the same source column. 

I am trying to generate a portfolio average (basically the average of my whole data set - overtime), that I can generate using different filters and then store. I then want to plot this average on graphs that continue to have a dynamic average calculating based on the filters I am selecting (while the original portfolio average doesn't change when I filter).

Apologies if this is not clear. I also tried CALCULATEDTABLES, but couldn't get this to work either, doesn't work well with existing measures. Any suggestions? 

Thanks for your help. 

[v-yuezhe-msft]

@SianM,

How about you use SUMMARIZE function to create a new table containing the measure? If you have questions about the DAX, please  share sample data of your table and post expected result.

Regards,
Lydia

[SianM]

Hi @v-yuezhe-msft,

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]

Hi @SianM,

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

=
ADDCOLUMNS (
    SUMMARIZE ( Table, Table[Column1], Table[Column2] ),
    "Column Name", [Measure]
)

[SianM]

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? @v-yuezhe-msft 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 · Accepted solution]

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]

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 @v-yuezhe-msft 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. 

Sian 

[SianM]

I have a data set where I want to calculate an average across the entire portfolio (and store this), and then include it on charts that can be filtered down by country or date and show the new dynamic average, against the portfolio average.  The portfolio average I don't want to change when the filters are edited, so a measure is not sufficient. 

When I use a calculated columns I end up with circular reference errors because they all use a common columns (as I need a number of these comparisons).  

I am really new to powerBI so I am probably missing something obvious. What is the best way for me to store the output I have generated from a measure (maybe in a different table) and then use that for my reports? 

[SianM]

Update - I have built them as table visualisations in PowerBI. Is it possible to just store these now as data source tables?