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

Re: Calculate total of category from different table.

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

Re: Calculate total of category from different table.

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
Community Support
Community Support

Re: Calculate total of category from different table.

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
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors