Forum Discussion

admin_xlsior's avatar
admin_xlsior
Post Prodigy
7 years ago
Solved

Need help : Visualization and filtering

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 G...
  • Anonymous's avatar
    Anonymous
    7 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]
    )