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've got a problem that despite excessive googling and even chat GPT I cannot for the life of me solve, I think I'm overcomplicating things. I have produced a dummy dataset which roughly matches the structure I have.
Table 1 (Revenues):
Month | Client | Rev |
Jan | AA | 200 |
Feb | AA | 200 |
Mar | AA | 200 |
Jan | BB | 300 |
Feb | BB | 300 |
Mar | BB | 300 |
Jan | CC | 400 |
Feb | CC | 400 |
Mar | CC | 400 |
Table 2 is just a client table
AA
BB
CC
Table 3 is forecast data:
Forecast Name | Month | Client | Value |
X | Jan | AA | 150 |
X | Feb | AA | 300 |
X | Mar | AA | 150 |
My desired output is:
Rev | Forecast | Difference (at Client level) | Pipeline (ER) | |
Jan | 900 | 150 | 0 | 900 |
Feb | 900 | 300 | -100 | 1000 |
Mar | 900 | 150 | 0 | 900 |
TOTAL | 2700 | 600 | -100 | 2800 |
Where difference is 0 if (Revenue - Forecast) for a client is > than 0 (as that means the forecast has been achieved). Pipeline is calculated as revenue - difference (if difference calc'd as a negative value)
In my structure Date and Client act as fact tables linked to both datasets. Due to the fact I don't possess a forecast for all clients I need it to calculate the difference at a client level but roll up at monthly level (there are also other filters in my dataset (industry etc)
Any help would be greatly appreciated
Thanks
Hi,
You may download my PBI file from here.
Hope this helps.
Thanks for this Ashish, I've spent far too much time on this
I've tested this and it returns the results I'm looking for as does @MartinMason1989 solution as far as I can see (they both produce the same results) - not sure which one is the better solution currently so I'm going to push them under different scenarios and see which one functions best
The problem can be solved if you create a star schema with the following structure with Clients and Months as dimension tables.
Definition of Difference (at client level) is following:
SUMX (
CROSSJOIN ( VALUES ( 'Clients'[Client] ), VALUES ( 'Months'[Month] ) ),
IF (
[Forecast] <> BLANK (),
IF ( [Revenue] > [Forecast], 0, [Revenue] - [Forecast] ),
BLANK ()
)
)
And definition of Pipeline (ER) is similar:
SUMX (
CROSSJOIN ( VALUES ( 'Clients'[Client] ), VALUES ( 'Months'[Month] ) ),
[Revenue] - [Difference (at client level)]
)
Produces the following table:
Thanks for this @MartinMason1989 I've spent far too much time solving for this - learnt some useful stuff.
That is the structure of my schema (well the actual one is more compiclated) but I was trying to distill the problem into its basics!
This solution works so far in my testing, as in my reply to @Ashish_Mathur they produce the same results, so now I'm going to stress test under different situations to see which one functions best before marking a solution.
Hi @Anonymous
Can you please explain how to calculate the Difference (at client level)? At present, I don't understand how to get these results. Perhaps you can use some Mathematics formula to help clarify the logic.
Best Regards,
Community Support Team _ Jing
User | Count |
---|---|
88 | |
84 | |
66 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |