cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Svillanueva94
Regular Visitor

Calculus between 2 unrelated tables

Hi, everyone!

My problem is that I want to make some math between two tables that have no relation. 

The first table only contains:

Column  Example
Service  Processing
Total cost   $  3,000,000.00
Year  2021
Month  1

 

And the second table contains:

Column  Example
Client  1
Product  Fuel
Date  01/01/2021
Sales  15

 

What I want to do is to get the proportional cost (of the total) to each client / product, like a rule of 3. 

I have tried creating a Measure that has the total sales (in this example is 8.5M), then another Measure that gets the unitary cost (total cost -3M / total sales - 8.5M), but when I want to do the last Measure, calculating the sales of each client / product * the unitary cost I'm getting the same number for all. What should I do?

1 ACCEPTED SOLUTION
v-yuaj-msft
Community Support
Community Support

Hi @Svillanueva94 ,

 

Based on your description, I created two sample table and did a test. I can reproduce your issue.

So I create a new measure as follows.

Measure = 

var x1=SUMX(FILTER(ALL('AA_Costos'),[Servicio]="Procesamiento"),[Pagado en Pesos SIN IVA])

var x2=CALCULATE(SUM('Autorizaciones'[Autorizaciones]),ALL())

var x3=x1/x2

return

SUMX(FILTER(ALL(Autorizaciones),[IdCliente]=SELECTEDVALUE(Autorizaciones[IdCliente])),[Autorizaciones])*x3

Result:

v-yuaj-msft_0-1617936906725.png

Hope that's what you were looking for.

Best Regards,

Yuna

 

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

View solution in original post

3 REPLIES 3
v-yuaj-msft
Community Support
Community Support

Hi @Svillanueva94 ,

 

Based on your description, I created two sample table and did a test. I can reproduce your issue.

So I create a new measure as follows.

Measure = 

var x1=SUMX(FILTER(ALL('AA_Costos'),[Servicio]="Procesamiento"),[Pagado en Pesos SIN IVA])

var x2=CALCULATE(SUM('Autorizaciones'[Autorizaciones]),ALL())

var x3=x1/x2

return

SUMX(FILTER(ALL(Autorizaciones),[IdCliente]=SELECTEDVALUE(Autorizaciones[IdCliente])),[Autorizaciones])*x3

Result:

v-yuaj-msft_0-1617936906725.png

Hope that's what you were looking for.

Best Regards,

Yuna

 

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

View solution in original post

selimovd
Community Champion
Community Champion

Hey @Svillanueva94 ,

 

maybe show us your tables, the result and the measures you used.

Then it's easier to help you.

 

In general when you want to compare things from 2 different table you can use data linage.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

The Measure that gets the total costs: 
Total Procesamiento = CALCULATE(SUM(AA_Costos[Pagado en Pesos SIN IVA]),AA_Costos[Servicio] = "Procesamiento") -- the amount is 3M
The Measure that gets the total sales:
Total autorizaciones # = CALCULATE( SUM(Autorizaciones[Autorizaciones])) -- the amount is 8.5M
The Measure that gets the unitary cost:
Unitario Procesamiento = [Total Procesamiento] / [Total autorizaciones #] -- the amount is 0.35
The last Measure, it tries to get the cost for each client:
Procesamiento = CALCULATE(SUM(Autorizaciones[Autorizaciones])) * [Unitario Procesamiento]
 
But, when I put then in a table, the Measure Procesamiento and the client is not working:
Svillanueva94_0-1617750714870.png
This are the screen shots of the tables:
The first table, with the costs (AA_Costos):
Svillanueva94_2-1617751190347.png

 

The second table, containing the sales of each Client (Autorizaciones):
Svillanueva94_1-1617751007103.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors