Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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.
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
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
User | Count |
---|---|
93 | |
86 | |
68 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |