Reply
Regular Visitor
Posts: 27
Registered: ‎05-08-2018
Accepted Solution

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


Accepted Solutions
Highlighted
Community Support Team
Posts: 3,808
Registered: ‎07-09-2016

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.

View solution in original post


All Replies
Regular Visitor
Posts: 27
Registered: ‎05-08-2018

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 

Highlighted
Community Support Team
Posts: 3,808
Registered: ‎07-09-2016

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.
Regular Visitor
Posts: 27
Registered: ‎05-08-2018

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