Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
You may try DISTINCTCOUNT and check this post.
https://www.dropbox.com/sh/rrcmf97oupdb3fr/AAAEW6-mDPVOjc6k5WBKS90ba?dl=0
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
You may try DISTINCTCOUNT and check this post.
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
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |