Sumif on multiple tables

[Anonymous]

I have 2 tables and I want to do a sumif on one table referencing the other. 

In excel I did it as =SUMIF(D4:D9,F4,C4:C9). 

In PowerBI, I thought it would be this, but is says SUMX cannot work with calues of type String. 

Capacity = sumx(filter(CapacityTable,CapacityTable[Graph]=TotalsTable[Graph]),D1D_Capacity[Existing Operating Capacity])

Any ideas? Thanks in advance.  

[StrategicSavvy · Accepted solution]

Hi, 

If you want to get aggregated table you need to create new table in PowerBI and try use this code for summarizing your data from Capacity Table:

Totals Table =
SUMMARIZE ( 'Capacity Table' ,
'Capacity Table'[Graph],
"Capacity",
SUMX('Capacity Table',
'Capacity Table'[Capacity]
)
)

Hope it helps!

[Daniel29195 · Accepted solution]

@Anonymous 

if this error is showing, cannot work with calues of type String.  then power bi is reading your capacity as text instaed of numbers . convert the datatype of your columns you are aggregating to number and try your measure.  this error should disappear.

let me know if this helps .. 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Ashish_Mathur · Accepted solution]

Hi,

If you want a Power Query solution, you can simply use the Group By feature and then use SUM as the aggregation function.  If you want a measure solution, you just need to drag Graph to the visual and write this measure

Measure = sum(D1D_Capacity[Existing Operating Capacity])

Hope this helps.

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can first create a relationship between two tables:

Then you only need to use this DAX to create a measure:

Measure = SUM('Table'[Capacity])

And put "Graph" and Measure into the table visual, the final output is as below:

Or you can use this DAX to create a new column in Table(2):

Column = 
CALCULATE(
    SUM('Table'[Capacity]),
    FILTER(
        'Table',
        'Table'[Graph] = 'Table (2)'[Graph]
    )
)

The final output is as below:

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

[Ashish_Mathur · Accepted solution]

Hi,

If you want a Power Query solution, you can simply use the Group By feature and then use SUM as the aggregation function.  If you want a measure solution, you just need to drag Graph to the visual and write this measure

Measure = sum(D1D_Capacity[Existing Operating Capacity])

Hope this helps.

[Daniel29195 · Accepted solution]

@Anonymous 

if this error is showing, cannot work with calues of type String.  then power bi is reading your capacity as text instaed of numbers . convert the datatype of your columns you are aggregating to number and try your measure.  this error should disappear.

let me know if this helps .. 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[StrategicSavvy · Accepted solution]

Hi, 

If you want to get aggregated table you need to create new table in PowerBI and try use this code for summarizing your data from Capacity Table:

Totals Table =
SUMMARIZE ( 'Capacity Table' ,
'Capacity Table'[Graph],
"Capacity",
SUMX('Capacity Table',
'Capacity Table'[Capacity]
)
)

Hope it helps!