Calculate Difference atone granularity and maintain for further calculations

[Anonymous]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

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

[MartinMason1989]

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:

[Anonymous]

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.

[v-jingzhang]

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