Calculate total of category from different table.

[mbmartin]

I want to calculate the cost of all campagins belonging to a category in relation of the signed contracts of a similar category in a different table. 

The data model. Categories are markedFor testing purposes should the CPO Paid measure display the corresponding budet

The cost are summarized in a campaign table. Each Campaign has a product it advertises. As a purchase facilitated by a campaign does not garantie that the product is of the same category the category of the product is logged in the purchase('Käufe') table. 

Both categories share the categories which are of interest ("Strom" and "Gas").  

These are the mearues I already tried.

CPO Paid = var tt = SELECTEDVALUE('Käufe'[Product Category];"ALL")
var budget = IF(tt="ALL";SUM('Datenbasierte Kampangien comb'[Cost]);CALCULATE(SUM('Datenbasierte Kampangien comb'[Cost]);FILTER(ALL('Kampagnen Zuordnung');'Kampagnen Zuordnung'[Vertragstyp]=tt)))//CALCULATE(SUM('Datenbasierte Kampangien comb'[Cost]);Filter('Datenbasierte Kampangien comb';SELECTEDVALUE('Käufe'[Product Category])="Strom");ALL('Käufe'[Product Category]))//if(SELECTEDVALUE('Käufe'[Product Category])="";SUM('Datenbasierte Kampangien comb'[Cost]);CALCULATE(SUM('Datenbasierte Kampangien comb'[Cost]);FILTER(ALL('Datenbasierte Kampangien comb'[Cost]);'Kampagnen Zuordnung'[Vertragstyp]="Strom");'Kampagnen Zuordnung'[Vertragstyp2]="Strom/gas"))
//; Filter(ALL('Kampagnen Zuordnung');'Kampagnen Zuordnung'[Vertragstyp]='Kampagnen Zuordnung'[Quelle]))
return budget//DIVIDE(budget;[Paid Abschlüsse];0)

I want to keep the calculation dynamic to allow easy changes in the future

[mbmartin · Accepted solution]

Managed to solve it. Had to first remove the filter applied by the visual with ALL() and then apply my own filter.

[v-lili6-msft]

hi  @mbmartin 

It's pleasant that your problem has been solved, please accept the reply as solution, that way, other community members will easily find the solution when they get same issue.

Regards,

Lin