cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Calculate total of category from different table.

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 markedThe data model. Categories are markedFor testing purposes should the CPO Paid measure display the corresponding budetFor 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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

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

View solution in original post

2 REPLIES 2
Highlighted
Frequent Visitor

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

View solution in original post

Highlighted

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

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors