Forum Discussion

danandr87's avatar
danandr87
New Member
4 years ago
Solved

Need help with Measure (Average, Summarize)

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?

 

  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    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.

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    danandr87 , Create a measure

     

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

    • danandr87's avatar
      danandr87
      New Member
      4 years ago

      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's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      danandr87
      New Member
      4 years ago

      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. 

       

       

       

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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!