Calculate Average Per day and More

[hola]

Hi All,

I am having trouble in calculating Average Per day for the matrix visualization. Please see the following screenshot for better understanding. I want to add two measures in the total Section of the matrix. 

1- Average Quantity Per day

2- On hand number of days supply  which wil be equal to  OnHand / AverageQuantitity Per day

Moreover, these meaures will also cater the number of days selected from the date filter. e.g if you see the first row in the following figure, then total quantity is 173 and on hand is 306 and from the date filter , 10 last days are selected. so

1 -Average Quantity Per day = total Quantity/ Number of days selected from the slicer = 173/10

2- On hand number of days supply = On Hand/ Average Quantity Per day = 306/ (173/10)

I am giving the link of pibx file. you can download the pibx file from the following link

https://drive.google.com/file/d/1ITik2Uv2BjQw21ryLbcDSyyXruLHnA5t/view?usp=sharing

Please let me know how to calculate these measures?

Thanks

Jerry

[Jihwan_Kim]

Hi, @hola 

I think the file in the link is deleted.

Please check.

[hola]

@Jihwan_Kim 

I have updated the link. Please see it now. thanks

[Jihwan_Kim · Accepted solution]

Hi, @hola 

I am not sure if I understood your data model correctly.

It was quite difficult for me to understand the concept of what is On Hand meaning.

I assumed that you need to use this number as a unique number, not using the number that is summing up in the selected period.

Also, I failed to understand the table structure, so I created a new one without the blank information that you had in your own table. And I created a dim-date table for the easy calcuation.

Please check the below picture and the sample pbix file's link.

All measures are in the sample pbix file.

https://www.dropbox.com/s/iguqnfo1rk99jvw/QueryAveragePerDay.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[hola]

Hey, thanks for your quick response. 

Yes. your assumption is correct. Onhand is the unique number and does not need to sum up in the selected period. thats why i added it as a column and not measure. 

Oh! the blank rows are by mistake in the table as I copied the date value later and did not notices that other fields are null. 

The solutions looks great to me however, there are two questions in that.

1- How to change the column title in the total section so instead of 'Quantity Fix', it should show 'Average Per day' and simiarly for the second column. 

2- My second question is that is it possible to show these columns with  the total columns which were present before? So the total number of columns will be 4 in the total section. 

Please let me know about these two things as well as I am new to power BI.

Thanks

[Jihwan_Kim]

Hi, @hola 

Thank you for your feedback.

If you change the measure name, you can see the column name in the matrix visualization also changes. I changed these already inside the pbix file link down below.

The higher hierarchy of columns in the matrix visualization can have the same number of columns as a total section. So, if you want 4 measures shown in the total section, then week18 has to show also 4 measures, for instance. In the sample pbix file, I manually made the width to almost zero for two columns in each week. You can check this in the below picture and the sample pbix file link down below.

https://www.dropbox.com/s/iguqnfo1rk99jvw/QueryAveragePerDay.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[hola]

There is one problem in this solution, If i change the number of days from the slicer then it shows other two columns for the weeks which are included now. So my first question is there any dynamic way of doing this? 

My second question is suppose we have two columns only in the matix with the name of  FixQuantity and FixAmount. I just would like to change the names of these two columns in the total section. is that possible?

Btw Many thanks for your quick response. I really appriciate the efforts.

Thanks

[Jihwan_Kim]

Hi, @hola 

Thank you for your feedback.

First Q: I think there is no way to fix this by using my current solution. The only way is to show everything first, adjust the width for everything, then come back to "latest 10 days show". 

Next time when 30 days is selected, then the width will remain as it is set. Just to be sure, turn off the Auto-size column width in the Format pane.

Second Q: I am not sure if I understood your question correctly, but It is not possible to show the different names by using the same measure. 

[hola]

okay, thanks I got all of my answers:)

[hola]

@Jihwan_Kim , there is one more question in daily average calcuation. 

Qty daily avg =

VAR startdateslicer =

MIN ( Dates[Date] )

VAR lastdateslicer =

MAX ( Dates[Date] )

VAR datesperiods =

CALCULATE ( COUNTROWS ( Dates ), ALLSELECTED ( Dates ) )

VAR quantitytotalinselectedperiod =

CALCULATE ( [Quantity Total], ALLSELECTED ( Dates ) )

RETURN

quantitytotalinselectedperiod / datesperiods

what is the purpose of startdateslicer and  lastdateslicer variables? it looks that these these variable are not being used in calculating the avg. could you please elaborate this as well?

thanks

[Jihwan_Kim]

Oh..

Sorry. That is my mistake.

That was the first attempt when I was using the initial version of the sample.

After I created the dim-date table and after I transformed the initial version, no need to include those. But I was trying to include those anyway. I forgot to delete those.

Thank you.