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) ...
  • 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.

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!

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    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.