Need help : Visualization and filtering

[admin_xlsior]

Hi all,

Need help or advice on my issue here.

I have 4 tables like below :

1. SalesFactTable

2. SalesGroup

3. Sales Target

4. Dates

SalesTables will have reference to Sales Group on Group Id field

and of course to Dates on Sales created date 

SAMPLE:

SalesFactTable

Sales Id	Created Date	Group	LineAmount
SO001	1/1/2016	Group1	100
SO001	1/1/2016	Group1	100
SO002	2/1/2016	Group2	100

SalesGroup

Group Id	Name
Group1	UK 1
Group2	UK 2
Group3	UK 3
Group4	UK 4

Sales Target will refer to Dates, it will only use the 1st day of the month (like below)

Sales Target

TargetDate	TargetAmount
1/1/2016	500
2/1/2016	600
3/1/2016	700

When create report, like Table visualitation, by dragging [Date]Month, [SalesGroup]GroupName, [SalesFactTable]LineAmount, I will have table like below :

Month	Group Name	LineAmount
Jan-16	UK 1	200
Feb-16	UK 2	100

This is understandable, since it will take a look at my fact table which only have those 2 months.

What if, when I want to add Target amount, how to make the visualization also show target of March because in my target there is the 3rd row, 3/1/2016.

Kindly advice,

Thanks in advance.

Robert

[Anonymous]

Robert-

Let's see if this is what you had in mind ( here is the pbix: https://1drv.ms/f/s!Amqd8ArUSwDSzy09GiLDPWyHv4Ve )

If so, or is close, follow these steps:

1. Need a dedicated Calendar Table in your model, this what we will put on the rows ( or any filters for that matter)
   1. I built a quick on in PQ, but if you click through the applied steps you will see what I did
2. Relate the new Calendar Table to both the SalesFact and SalesTarget tables:
3. 
4. Then just easy measures summing the Target Amount and Sales Amount:

5. Total Taraget Amount = SUM ( FactSalesTarget[TargetAmount] )
   
   Total Sales Amount = SUM ( SalesFactTable[LineAmount] )

[admin_xlsior]

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,

[Anonymous · Accepted solution]

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_xlsior]

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.