cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sinanalmac Regular Visitor
Regular Visitor

Find the monthly value equalin the Target table accoding Positive and negetive values

Hi All,
I'm  working  forecasting project  and I 'm stuck in  the middle of somewhere maybe you guys could help me

I have Forecasting values, Category  SubCategory, All country target data. What am I trying to do? 

I want to see How many months have I reached all country, category, and subcategory targets according to BIAS  values  

if you want  you can find my files  in this  link https://www.dropbox.com/sh/rrcmf97oupdb3fr/AAAEW6-mDPVOjc6k5WBKS90ba?dl=0

I have a Bias Calculate  and I can Compare  With  results with target values  
 
BIAS = SUM('FORECAST BIAS'[Sales])/SUM('FORECAST BIAS'[ Forecast])-1 

I added two filters   Category and Subcategory. Values are changed  by  this selections 
When I  chose noting   the values Compares  with the country  target  in  the Country Target table 
When I  chose the category, the values Compares  with the category target  in the Category Target table

When I  chose the Subcategory, the values Compares  with the category target  in the SubCategory Target table

 

 

for  this calculation, I created a measure and  typed this

Calculation Category =IF(ISBLANK(SELECTEDVALUE('FORECAST BIAS'[Category ]))=FALSE(); AND('FORECAST BIAS'[BIAS]<MAX('Category Target'[ForecastBias Target]);'FORECAST BIAS'[BIAS]>-MAX('Category Target'[ForecastBias Target]));1;IF(ISBLANK(SELECTEDVALUE('FORECAST BIAS'[SubCategory]))=FALSE(); AND('FORECAST BIAS'[BIAS]<MAX(SubcategoryTarget[ForecastBias Target]);'FORECAST BIAS'[BIAS]>-MAX('SubcategoryTarget'[ForecastBias Target]);1;0)
Why  I typed Like this 

Because Forecast bias targets are in positive and negative values  For example -0,05  and 0,05 

but it is not working correctly.  if you have any Suggestion ??  


Best  regards
Sinan 

 

 

 


 

 

targets.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Find the monthly value equalin the Target table accoding Positive and negetive values

@sinanalmac,

 

You may try DISTINCTCOUNT and check this post.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
sinanalmac Regular Visitor
Regular Visitor

Re: Find the monthly value equalin the Target table accoding Positive and negetive values

 https://www.dropbox.com/sh/rrcmf97oupdb3fr/AAAEW6-mDPVOjc6k5WBKS90ba?dl=0

target.JPG


Hi ALL, I found a solution for the calculation.  Which I  I stuck. except for one problem, I updated  Pbix File again. 

What am I done?
I created  Three  Measure  
CATEGORYTARGET = IF(AND('FORECAST BIAS'[BIAS]<MAX('Category Target'[ForecastBias Target]);'FORECAST BIAS'[BIAS]>-MAX('Category Target'[ForecastBias Target]));1;0)
COUNTRY TARGET = IF(AND('FORECAST BIAS'[BIAS]<MAX('Country Target'[ForecastBias Target]);'FORECAST BIAS'[BIAS]>-MAX('Country Target'[ForecastBias Target]));1;0)
SUBCATTARGET = IF(AND('FORECAST BIAS'[BIAS]<MAX('SubcategoryTarget'[ForecastBias Target]);'FORECAST BIAS'[BIAS]>-MAX('SubcategoryTarget'[ForecastBias Target]));1;0)

Then   

I created A measure   that merged  of them
CALCULATION TARGET = IF(ISBLANK(SELECTEDVALUE('FORECAST BIAS'[Category ]))=FALSE();IF(ISBLANK(SELECTEDVALUE('FORECAST BIAS'[SubCategory]))=FALSE();[SUBCATTARGET];[CATEGORYTARGET]);[COUNTRY TARGET])

This Solution is giving me The result That  I want. But there is a small problem in this file. I can't Calculate Count of the month.  İn a card gadget.
Actually, I can see the right result in table gadget.  When  I select count of the month   the result is  wrong 

Community Support Team
Community Support Team

Re: Find the monthly value equalin the Target table accoding Positive and negetive values

@sinanalmac,

 

You may try DISTINCTCOUNT and check this post.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sinanalmac Regular Visitor
Regular Visitor

Re: Find the monthly value equalin the Target table accoding Positive and negetive values

hi @v-chuncz-msft

 

 

Thank you  for your help. I solved  my problem. But I'm going to  look at  your Solution after sent this message
 
Best Regards

Sinan