Automatically create measure for new data in column

[dwerff]

In my report, I have to create the same measure for all unique values of a column. The data is refreshed regularly and new unique values may occur in the column with each data refresh. Is it possible to automatically generate that measure for each new value in the column without having to interfere manually?

E.g. I have a data set that stores prices of a product per competitor per date. For each competitor I want to find the average price (differentiating by product is not (yet) relevant). Right now, I have added those measures for company A, B, and C manually:

When the data is updated, new companies (company D) can enter the market:

I don't want have to change the pbix file everytime and manually create the measure for the average price whenever a new company enters (or leaves) the market. That won't even be possible when the data and report are refreshed automatically on a regular (daily) basis. 

Is there a way to automate this process in PowerBI, so it recognizes that there are new values in the Company column and creates a measure for these companies (and possibly even puts those measures in the right visuals)? 

[MartynRamsden]

Hi @dwerff 

Unfortunately, there's no way to automatically create measures in Power BI.

However, I think you can probably achieive what you're looking for with a single 'Avg Price' measure (as long as the Customer column is in the Filter Context).

What's the end goal? Can you explain or show what the report should look like and which visuals are being used?

[dwerff]

Hi @MartynRamsden,

I want to create a multiple-page report containing visuals such as average price and price position over time, as well as more complicated visuals of percentual price differences between companies at specific price positions or between specific pre-defined companies (such as the ones that have just entered the market). Therefore, I have to have indivudual and separate measures for each company. 

This report already exists, but one of the general visuals showed a line for a new company that didn't show up in any of the other visuals because the new company measure doesn't explicitely exist yet and couldn't be included in the individual visuals. 

If measures can't be created automatically, what else could be a good solution?

[MartynRamsden]

Without having the benefit of seeing your report, I’m assuming that you have a single table your data model?!

By following best practice and using a star schema, you should be able to use a simple measure, like the one below, and rely on the filter context to calculate the correct value per Company.

Avg Price = AVERAGE ( Table[Price] )

Things get a little trickier when you want to compare against a specific set of companies but with the right data model, it makes life so much easier.

For example, if you have a ‘Company’ dimension table, you could add a column to identify which companies should be used for comparison. It’s then just a case of writing another measure to calculate the average price for those:

Avg Price For Comparison =
CALCULATE (
    [Avg Price],
    REMOVEFILTERS ( 'Company'[Company] ),
    Company[UseForComparison] = "Y"
)

You can then create another measure to calculate your % difference, which references the previous measures.

Sorry I don't have a simple answer for you but if you get your data model right, everything else will be a lot easier.

Best regards,

Martyn