Need help with Measure (Average, Summarize)

[danandr87]

Hi there,

i am quite new to Power Bi and need some helf for a specific measure i want to create.

This is my simplified data model:

I have a Fact Table with the goods issues (GI) und open requirements (RQ) of my stock keeping units with quantity and date. And i have a Date Table linked with the Fact Table. In the Date Table there is a column YYMM which is the combination of year and month. And i have a column PERIOD which indicates the months i want to consider for building the measure.

So this is what I want to do now:

I want to create a stacked column chart with my open Requirements in the future. And in this chart I want to display a line with the average goods issues per month in the determined time range (months with PERIOD = X). 

But i have no idea how to create the measure for this ignoring the row/filter context. Can you help me?

[tamerj1]

Hi @danandr87 

Try

TOTAL_QTY_GI =
CALCULATE (
[TOTAL_QTY],
FILTER ( DateTable, DateTable[PERIOD] = "X" ),
FactTable[Type] = "GI",

ALL ( DateTable[YYMM] )
)

TOTAL_QTY_RQ =
CALCULATE ( [TOTAL_QTY], FactTable[Type] = "RQ" )

[danandr87]

Hi tamerj1,

thanks for your answer, but this does not meet my requirements.

I tried to explane my requirement in the following screenshot. I need the measure described with the red lines and frames. Your supposed measure got me the result in the green frame. 

[V-lianl-msft · Accepted solution]

Hi @danandr87 ,

Try to create a measure like below:

Measure 2 = 
var total = 
CALCULATE([Measure],FILTER(ALL('Table'),'Table'[Period]="X"))
var count_x =CALCULATE(DISTINCTCOUNT('Table'[YYMM]),FILTER(ALL('Table'),NOT(ISBLANK([Measure]))&&'Table'[Period]="X"))
return DIVIDE(total,count_x,0)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

@danandr87 
Sorry I misunderstood your requirement.
Try 

NEW MEASURE =
AVERAGEX (
    ALL ( DateTable[YYMM] ),
    CALCULATE ( SUM ( FactTable[QTY] ), FactTable[Type] = "GI" )
)

Still I did not understand the purpose of the DateTable[Period] column. As it looks you did not include with your calculation. You can upload a file with sample insensitive data to WeTransfer for example and share the download link. This way will be able to provide you with much more reliable support.
Thank you!

[amitchandak]

@danandr87 , Create a measure

calculate(Averagex(values('Date'[YYMM]), calculate(sum(Table[QTY])) ) , allselected())

[danandr87]

thanks for this answer, but it doesnt meet my requirement. For calculation of the average I want the quantities aggregated per month, but only for the months with PERIOD = X. But these months are not the months in my chart. So the calcualtion must somehow happen in the background ignoring any row and filter contexts, only considering the SKU as a filtering parameter.

[amitchandak]

@danandr87
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[danandr87]

I don't know how to attach a file to this forum and i am not allowed to upload files into a cloud with my work computer. But i took some screenhots of my simplified data model.

FactTable:

DateTable:

This is my requirement: