Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
here is my sample data:
Solved! Go to 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.
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.
@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])))
no, value not changing
@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))
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
Hi @froxas ,
Please try this:
Measure = CALCULATE(SUM('transaction'[amount]),FILTER('transaction',NOT(ISBLANK('transaction'[insured]))))
The result will be something like below:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |