Sum of bigger sum

[th_moreira]

Hi. Greetings from Brazil.

I am getting troubles to get the expected return. The situation is below:

- I have two different fact tables: one for accounting, and another for purchase order values

PO

Accounting

- I have then created dimension tables - calendar and code (code is like an application code.)

- I need to sum PO values, and accounting for each application. No problem with that.

- For each application, I also need to report biggest sum value - if PO is bigger, PO value; if accounting is bigger accounting value

I have created a Measure: 

Measure =

  IF( SUM(fPO[TOTAL]) > SUM(fAccounting[Value]),

  SUM(fPO[TOTAL]),

  SUM(fAccounting[Value]

  ))

This works very well when I have only one application code selected. But when I clear the filter, or filter more than one code, I have the sum of data, before the compare; when I need to compare line by line, and then sum. Using the same example dataset:

returning biggest sum, the return is 203, when I want to return 208.

 

- I already have tried a column on application dimension table, but as I don't have a date column there, the sum ignores time filtering. So, just not works.

How to get that? Any idea?

Thanks for any help with that.

Thiago.

[v-janeyg-msft · Accepted solution]

Hi， @th_moreira 

It’s my pleasure to answer for you.

According to your description, I think you can create a measure with'summarize'.

Like this:

Measure 3 =
SUMX (
    SUMMARIZE (
        'Table (3)',
        'Table (3)'[code],
        "aaa",
            IF (
                SUM ( 'Table'[po value] ) > SUM ( 'Table (2)'[acc value] ),
                SUM ( 'Table'[po value] ),
                SUM ( 'Table (2)'[acc value] )
            )
    ),
    [aaa]
)

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[th_moreira]

Wow, thats exactly what I need. 

Thank you. 

[amitchandak]

@th_moreira , try a measures like

sumx(values(Table[code]), IF( SUM(fPO[TOTAL]) > SUM(fAccounting[Value]),
SUM(fPO[TOTAL]),
SUM(fAccounting[Value]
)))

[th_moreira]

Hi. 

Thanks for your reply. Almost. The problem is when no application code is selected but other are (like dates, for example), when I think the sum is happening for all lines. Individually is correct.

New ideas?