help with a DAX formula

[froxas]

I have invoices table which connects to transaction table.

I need to calculate two measures per contract:

1. sum of amount (that works fine)

2. sum of amount insured = it is exactly the same as "sum of amount", but it only needs to calculate these invoices where sum of insured is > 0. it means in below example it do not need to include invoice I003 and I009

this i do with a formula, but it wont work.

sum of amount insured = CALCULATE([sum of amount], FILTER(invoice, SUM('transaction'[insured]) > 0))

here is my sample data:

sample 

[v-alq-msft · Accepted solution]

Hi, @froxas 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

Table2:

You may create a measure as below.

amount insured = 
var tab = 
SUMMARIZE(
    'Table2',
    'Table2'[invoice_number],
    "Sum",
    SUM('Table2'[amount]),
    "Flag",
    var val = 
    SUM('Table2'[insured])
    return
    IF(
        ISBLANK(val),
        0,1
    )
)
return
SUMX(
    FILTER(
        tab,
        [Flag]=1
    ),
    [Sum]
)

Result:

Best Regards

Allan

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

[amitchandak]

@froxas , Try a measure like

calculate(sum(Table[amount]), filter(values(Table[invoice_number]), calculate(sum(Table[insure])) >0))

or

sumx(filter(values(Table[invoice_number]), calculate(sum(Table[insure])) >0),calculate(sum(Table[amount])))

[froxas]

no, value not changing

[amitchandak]

@froxas , I tried this. Seems to be working. File attached after signature

sum of amount insured = CALCULATE([sum of amount], FILTER(VALUES(invoice[invoice_number]), SUM('transaction'[insured]) > 0))

[froxas]

maybe i explained wrong.

the result needs to go in the table marked in red.

and the result needs to be "sum of amount" per contract, but only sum these invoices where invoice total "sum of insured" > 0.

it means below table: only invoices I001 and I007 has sum of insured >0, therefore their amount needs to be summed only.

then the result needs to be : 

contrac1 - 300

contrac2 - 500

total: 800

[PC2790]

Hi @froxas ,

Please try this:

Measure = CALCULATE(SUM('transaction'[amount]),FILTER('transaction',NOT(ISBLANK('transaction'[insured]))))

The result will be something like below: