Calculate average with gaps of data

[Anonymous]

Hello ,

I have a fact table that contains this two value 

i create a measure that return the Qty on hand through the year as below the result of my measure:

Now , i want to calculate the average of Qty on hand (previous result) .

I create a new measure avg , that call the previous measure like as below : 

average of Qty on hand = averagex('Fact table',[Qty on hand])

As below the result : 

The result that i get it isn't what i want because i get only that average (238+298)/2 

While  i want to have an average or all the row  ((238*7)+(298*5))/12

FYI : I want also that measure work with another context for example if i choose another dimension 

Any idea how can i do that ? 

Thanks for help !! 

[MFelix]

Hi @Anonymous ,

You need to make the average based on the months values somethign similar to:

average of Qty on hand = averagex(Values('Fact table'[Month]),[Qty on hand])

[Anonymous]

@MFelix  the granularity in my fact table is date like as below :

But what if i want to work for all the level , i need to create all measure for different level ?

i will have a lot of measure ...... There are any solution to optimize that ? 

Thanks 

[V-lianl-msft]

Hi @Anonymous ,

Has the problem been solved? If the problem still exists, could you provide a sample pbix for us to do more tests.

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

[Anonymous]

Hi @V-lianl-msft 
Thanks for the reply.
the model is based on SSAS tabular model i don't think so the pbix can help you because i use a live connection to the cube.
Well, the proposed solution by @MFelix  worked only with month but when i use another level of date the average doesn't work correctly

for example if i want to analyse the Average of qty on hand by week or Quarter or day ? 
i need to create anothers measure for that ? like as below : 

average of Qty on hand Week = averagex(Values(Calendar[Week]),[Qty on hand])

average of Qty on hand Quarter = averagex(Values(Calendar[Quarter]),[Qty on hand])

I will have a lot of measure because i need to create average for 10 measure and if i want to cover Month , Week and Quarter i will have 30 measures for average .
There are anoter solution to optimize that ? 

[MFelix]

Hi @Anonymous ,

In this case you need to use calculation groups with the same sintax for each of the periods quarter, month and week and using the calculation groups you are abble to use it whit any measures at once (in this case 30).

https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/

[MFelix]

Hi @Anonymous ,

How is your model setup? do you have a calendar table? 

You create measures in order to not have to create one for each level of course this depends on the result you want to achieve.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.