Forum Discussion

AndreaLorenzo's avatar
AndreaLorenzo
Frequent Visitor
2 years ago

Using TREATAS for different granularity levels

Hi,

I'm struggling with a common problem (two tables with different granularity) and I hope someone could help me.

I need to comparte the actual sales pending invoice amount with the budgeted one, but the sales fact table is way more detailed than the budget one.


In my model, I've got:

 1) Sales fact fable, with columns   Year, Month, Week, Customer_Id, Area_Id, Pending_Sales_Amount

 2) Goals table, with columns   Year, Id_Area, Pending_Goal

 

I've been re searching and I know I can use TREATAS, but I'm not able to build the correct DAX expression.

 

Any help would be appreciated, thanks

 

 

 

Need Help

3 Replies

  • Joe_Barry's avatar
    Joe_Barry
    Solution Sage
    2 years ago

    Hi AndreaLorenzo 

     

    If you want to visualise the comparisons on a line graph for example with Month in the x Axis, then your Budget table will need to be broken down into months.

     

    In Power Query you can do the following in your Goals Table for a monthly overview. 

     

    Create a custom column to get a monthly amount

    [Pending_Goal] / 12

    Create another column called DateKey and Enter

    "01/01/" & [Year]

    convert to Date

     

    Create a custom column called intervals with this formula

    List.Numbers(1,12)

    Expand the column

     

    Now we need to create a date column for each interval called Month

    Date.StartOfMonth(
  Date.AddMonths(
    [DateKey],
    [Intervals] -1
  )
))

    Convert to date

     

    You will have a monthly amount for each month of the year

     

    Load the table and create a one to many relationship from the date table to the Month column in Goal table

     

    Create a measure to  calculate the Goal amount 

    Pending Goal = SUM('Goals'[MonthlyAmount]

    Create a similar Measure from your Sales table and add the Dates from your date table to a visual and then the two measures and you will then get a comparison.

     

    These measures will be the basis of all your measures.

     

    Thanks

    Joe

     

    If you found my answer helpful and it solved your issue, please accept as solution

    • AndreaLorenzo's avatar
      AndreaLorenzo
      Frequent Visitor
      2 years ago

      Hi Joe_Barry ,

       

      Thanks for the idea. Unfortunately, I can not split the goal into 12 months as it doesn't make sense in my scenario.

       

      I will have to keep on investigating.

       

      Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi AndreaLorenzo ,

     

    Please try this:

    Sales_vs_Goals = 
CALCULATE(
    SUM(Sales[Pending_Sales_Amount]),
    TREATAS(
        SUMMARIZE(ALLSELECTED(Goals), Goals[Year], Goals[Id_Area], "Pending_Goal", SUM(Goals[Pending_Goal])),
        Sales[Year],
        Sales[Area_Id]
    )
)

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.