Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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:
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.
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:
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
96 | |
79 | |
65 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |