Forum Discussion
Need help : Visualization and filtering
- Anonymous7 years ago
Since there is no data about groups in the target table, we cannot directly filter target based on groups. There are some different ways to allocate the target over groups ( i'd highly recommend this page on how that can be accomplished https://www.daxpatterns.com/budget-patterns/ )
But if here's what I came up with in the meantime:
As you can see, there is the target for 3/1/2016 for each group (because group id cannot reach target). so an idea would be to take the amount of each group and divide the total target, but that's just an idea.
Here's the DAX for the above measure:
Total Target Checking = //Calculates the Last Date in the Target Table Var __LastDate = CALCULATE( LASTDATE(FactSalesTarget[TargetDate]), Filter( ALL ( DimCalendar[Date]), LASTDATE(DimCalendar[Date]))) Return //Checks to see if the LastDate is the same date in the current filter context Var __LastDate_Equals_Current_Date = __LastDate = Max(DimCalendar[Date]) Return /*Checks to see if the Last date is greate than the date in the current filter context if it is, then also check to see if the Total Sales are Not blank*/ Var __EarlierDate_NotBlankSales = AND( __LastDate > Max( DimCalendar[Date]), NOT( ISBLANK( [Total Sales Amount])) ) Return /* Final fucntion, if either of the above are true, then give the total target, if not, then give nothing */ IF ( OR( __LastDate_Equals_Current_Date, __EarlierDate_NotBlankSales ), [Total Target] )
Hi Nick,
Thank you very much for such easy explanation and prompt reply.
Yes, it is just what I had in mind. Although there is one flaw when I add Sales Group then the target will be repeated for all existed group (UK 1 until UK 4)
I just tried to use Calculate instead and filter it by SalesGroup[GroupId], but it return the same repeating target amount for all group.
Before this, I tried to add SalesGroup in my SalesTarget, and it seems worked. Only it means my SalesTarget should be very detail per all existed SalesGroup, which means not effective, the sales group can grow as many as user like. Is my sales target really need to be that detail ?
Thanks,
Since there is no data about groups in the target table, we cannot directly filter target based on groups. There are some different ways to allocate the target over groups ( i'd highly recommend this page on how that can be accomplished https://www.daxpatterns.com/budget-patterns/ )
But if here's what I came up with in the meantime:
As you can see, there is the target for 3/1/2016 for each group (because group id cannot reach target). so an idea would be to take the amount of each group and divide the total target, but that's just an idea.
Here's the DAX for the above measure:
Total Target Checking =
//Calculates the Last Date in the Target Table
Var __LastDate =
CALCULATE(
LASTDATE(FactSalesTarget[TargetDate]),
Filter(
ALL ( DimCalendar[Date]),
LASTDATE(DimCalendar[Date])))
Return
//Checks to see if the LastDate is the same date in the current filter context
Var __LastDate_Equals_Current_Date =
__LastDate = Max(DimCalendar[Date])
Return
/*Checks to see if the Last date is greate than the date in the current filter context
if it is, then also check to see if the Total Sales are Not blank*/
Var __EarlierDate_NotBlankSales =
AND(
__LastDate > Max( DimCalendar[Date]),
NOT( ISBLANK( [Total Sales Amount]))
)
Return
/* Final fucntion, if either of the above are true, then give the total target,
if not, then give nothing */
IF (
OR(
__LastDate_Equals_Current_Date,
__EarlierDate_NotBlankSales
),
[Total Target]
)- admin_xlsior7 years agoPost Prodigy
Hi Nick,
Thanks!
Really appreciated your guidance, it help me a lot.
Btw, also thanks for the link, it's very cool and interesting to learn more from there.
:smileyvery-happy: