Using TREATAS for different granularity levels

[AndreaLorenzo]

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

[Anonymous]

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. 

[Joe_Barry]

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]

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