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.
Hi all - sorry if this is a simple one, or posted and answered before!
New user to power bi - trying to figure out how to do a sumif equivalent formula
I have two tables, one is the raw data and the second is the summary data (example below)
I need a summary of the raw recovered field based on the individual 'job', calculated to the 'tot_recov' fields
raw | summary | |||||||
job | from | to | recovered | job | depth | tot_recov | ||
h1 | 0 | 10 | 9.8 | h1 | 24.8 | |||
h1 | 10 | 14.8 | 4.7 | h2 | 12 | |||
h1 | 14.8 | 16.9 | 2.1 | h3 | 16.2 | |||
h1 | 16.9 | 24.8 | 7.8 | |||||
h2 | 3 | 6.5 | 3.5 | |||||
h2 | 6.5 | 9 | 2.4 | |||||
h2 | 9 | 12 | 2.8 | |||||
h3 | 0 | 9 | 8.8 | |||||
h3 | 9 | 10.8 | 1.6 | |||||
h3 | 10.8 | 15 | 4.2 | |||||
h3 | 15 | 16.2 | 1.1 | |||||
in excel, I would use a simple sumif equation in the tot_recov field, like; =SUMIF(A:A,G3,D:D)
so I figure in power bi something like; = CALCULATE(SUM('raw'[recovered]),'raw'[job]='summary'[job])
however, this fails due to "expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."
I figure this is meaning it would like a single criteria (eg. 'raw'[job]="h1" or along these lines), but this is illogical for this data set as there is approx. 500 individual jobs
data is from 2 different data sources but linked through the 'job' field
Thanks in advance!
Hi @aJamesmac ,
You can use the below Measure.
Measure1 = CALCULATE(SUM('raw'[recovered]), ALLEXCEPT( 'raw', raw[job])
else create a TABLE
Summary TABLE = ADDCOLUMNS (
SUMMARIZE ( 'raw', raw[job]),
"depth" , CALCULATE(SUM('raw'[recovered]), ALLEXCEPT( 'raw', raw[job]))
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
@aJamesmac , You can use Sumx
Sumx(Table,<expression>)
Here you can use filter in Table and You can use if in expression
https://docs.microsoft.com/en-us/dax/sumx-function-dax
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 |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |