cancel
Showing results for 
Search instead for 
Did you mean: 

Problem with dynamic calculation in PowerBI

Hi everyone,

 

My problem is relatively easy to understand but I didn't find any good way to resolve it...

 

I have a table of data with a numeric column called Value associated with different Account, Date and others criterias...

I would like to screen the average for each Month-Year of the cumulative value by day for the column Value.

 

For example without any filters for Jan-2015 the result would be: ~ 0.516  (0*11 days + 1*10 days + 0*7 days + 2*3 days)/31 days

 

But the calculation has to be refreshed each time a user change its filters, it has to be dynamic Robot Mad, so I think it should be a measure. 

 

For example:

- if only the Account "A" is selected then the calculation for Jan-2015 would be: ~ 0.323  (0*11 days + 1*10 days + 0*10days )/31 days

- if the account A and the CatA "XX" are selected then the calculation for Jan-2015 would be: ~ 0.645  (0*11 days + 1*20 days)/31 days

- For Fev-2015, if the Account "B" and the CatB "PP" are selected then the result would be:

 ~ -0.034  ( 0*28 days + 2* 15days + (-2)* 16 days ) / 59 days

 

Thank you in advance for your help !!

 

Data.PNG

Status: New
Comments
Moderator

Hi @DCabaud,

 

Would you please clarify the logic of below bold section? 

 

"For example:

- if only the Account "A" is selected then the calculation for Jan-2015 would be: ~ 0.323  (0*11 days + 1*10 days + 0*10days )/31 days

- if the account A and the CatA "XX" are selected then the calculation for Jan-2015 would be: ~ 0.645  (0*11 days + 1*20 days)/31 days

- For Fev-2015, if the Account "B" and the CatB "PP" are selected then the result would be:

 ~ -0.034  ( 0*28 days + 2* 15days + (-2)* 16 days ) / 59 days"

 

 

Best Regards,
Qiuyun Yu