Totals combining sums from two different fact tables: 'Estimates' and 'Sales'?

[PaulDBrown]

Hello everyone,

I kndly request help to solve what must be a very common scenario: combining and computing data from different tables to compute totals. In this case, the model is basically a sales estimates table, a sales table and two lookup tables (YearMonth - due to different granularity), and an ITEM table. 

The 'rogue' measure in the picture  is "Measure: Estimates, or if not sales". This measure basically computes the value for an 'estimate', but if the 'estimate' is empty/0, then it returns the value in 'sales'.

And of course the subtotals and total don't add up; the value computed is the value from the Estimates measure.

So how can I obtain the correct subtotals and total for this measure?

Thank you for your help (it's driving me mad...)

Regards,

Paul.

[PaulDBrown · Accepted solution]

@v-shex-msft

I've worked it out!!!

It's taken me a while (days in fact...) but I've finally found out how to get the right values/totals in the right places. I'ts basically nested SUMX. 

For those who might come across the same problem, the steps I've taken are as follows:

1) to obtain the initial results (see the problem in the first thread, the measure computing the 'Result' column is a simple IF function

2) to obtain the correct totals by month, I used SUMX in the expression SUMX ITEM = SUMX('ITEM'; [result])

3) finally to obtain the correct 'total', with everything else in place, I nested the previous expression in a new SUMX, this time by month: SUMX('MONTH'; [SUMX ITEM])

et voilà!

The amount of time I would have saved if I had come across this solution ages ago...

[PaulDBrown]

Please, anyone?

[v-shex-msft]

Hi @PaulDBrown,

I think you can create a new table with summary 2018 estimates records and sales records, then use new table to create matrix visual.

Table = 
ADDCOLUMNS (
    '2018 Sales',
    "Sales",
    VAR temp =
        SUMX (
            FILTER (
                ALL ( Sales ),
                [Item] = EARLIER ( '2018 Sales'[Item] )
                    && [Month] = EARLIER ( '2018 Sales'[Month] )
            ),
            [Sales]
        )
    RETURN
        IF ( temp <> BLANK (), temp, [Estimates] )
)

Regards,

Xiaoxin Sheng

[PaulDBrown]

@v-shex-msft

Thank you Xiaoxin for the suggestion! I actually have been trying to avoid creating new tables since, from what I've read, it's not the most efficient solution (performance-wise). I will definitely keep your suggestion in my "toolset" if I'm unable to find a 'measure'-way round this.

I have been playing around with SUMX (SUMX ITEM = SUMX('ITEM';[Result])) and have achieved a limited degree of success: the measure delivers at the "month" level, but the total is till not correct (quite baffling to me). (see below)

Any possible solutions to obtain the grand total?

[PaulDBrown · Accepted solution]

@v-shex-msft

I've worked it out!!!

It's taken me a while (days in fact...) but I've finally found out how to get the right values/totals in the right places. I'ts basically nested SUMX. 

For those who might come across the same problem, the steps I've taken are as follows:

1) to obtain the initial results (see the problem in the first thread, the measure computing the 'Result' column is a simple IF function

2) to obtain the correct totals by month, I used SUMX in the expression SUMX ITEM = SUMX('ITEM'; [result])

3) finally to obtain the correct 'total', with everything else in place, I nested the previous expression in a new SUMX, this time by month: SUMX('MONTH'; [SUMX ITEM])

et voilà!

The amount of time I would have saved if I had come across this solution ages ago...

[PaulDBrown · Accepted solution]

As a follow-up, I just read in Matt Allington's website (exceleratorbi.com) that nested SUMX is inefficient, and followed an example he posted talking about nested SUMX and the better option of: SUMMARIZE

Thank you @MattAllington for your very helpful blog and examples!