Get average from a measure in datamodel

[Yvalson]

Hi All,

I'm currently working with a datamodel that was created by our BI partner.

This model has  a measure called "Margin" that calculates the margin on orders/products etc.. based on relations in the datamodel.

I want to make a measure that gets the average margin in a period. For example I have a page where I can select a week and it will show information about that week.

The "AVERAGE" function in PBI needs to refer to a table which I don't have. Is it possible to still get the average margin or is this not possible in PBI?

Measure would look something like this:

Avg_Margin = AVERAGE(Margin)

Thanks in advance!

[Anonymous · Accepted solution]

HI @Yvalson,

How did these two table relationship mapping? Can you please share some more detail infoatmaiosn about these? They will help us clarify your caeiro and test to coding formulas.

How to Get Your Question Answered Quickly  

BTW, measure expression cannot directly aggregate with other functions. You can create a variable table with this formula to stay the specific result based on current category group.

Then you can use iterator aggregate function to apply the second level aggregation on it.

formula =
VAR selection =
    ALLSELECTED ( Calendar[Date] )
VAR summary =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( Table1 ), [Date] IN selection ),
        [Date],
        "cMargin", [Margin]
    )
RETURN
    AVERAGEX ( summary, [cMargin] )

Regards,

Xiaoxin Sheng