DIVIDE two tables measure

[arquironsalud]

I turn to the wisdom of this forum because I have a question that I can not solve and I'm going crazy. I am going to describe the structure of my data and then I will explain the problem I have. If you need any clarification, do not hesitate to let me know.

I have two tables:
-The first one is called ACCOUNTS and contains the following columns: "15 DIG" (text), "1 DIG" (text) "AGGREGATOR RATIOS" (text) 
-The second one is called BALANCES and contains the following columns: "COD ACCOUNT 1" (text), "ACCOUNT 15" (text) and "IMPORT" (currency).
The relationship between both tables is ACCOUNTS[15 DIG] with BALANCES[ACCOUNT 15]. I show an explanatory image below.

In addition, I have created two measures.
The measure COSTS is the sum of BALANCES[IMPORT] when BALANCES[COD ACCOUNT 1]="6". If we consider the above BALANCE table as an example, COSTS would be 9260 (coming from 160+4100+5000).
The measure SALES is the summation of BALANCES[AMOUNTS] when BALANCES[CODE ACCOUNT 1]="7". If we consider the above BALANCE table as an example, COSTS would be 80.

I have created a visual object of type matrix that shows the following:

And finally we come to my problem, which is none other than the "PESO" measurement that you see in the image. I need this measure to return COSTS/SALES for each AGGREGATOR RATIOS, where SALES is always the same number, and COSTS varies according to the AGGREGATOR RATIOS (which can be APROVISIONAMIENTO, FARMACIA, MAT. SANITARIO, etc.).

As an example of the result of the previous image, the PESO for APROVISIONAMIENTOS should be 20.16% (comes from 34319945 / 170162096), 10.49% for SUBCONTRATACIONES (comes from 17856677 / 170162096).

As you can see, I need you to tell me the DAX of PESO, since I always get 0, and I don't know why.

Thank you very much in advance.

[tamerj1]

@arquironsalud 

Please try

PESO =
DIVIDE ( [COSTS], CALCULATE ( [SALES], REMOVEFILTERS () ) )

[arquironsalud]

It has changed somewhat, at least it is no longer all zeros, but the percentages are wrong. In the case of OTROS COSTES if we do the operation 9169273 / 170162096, the result should be 5.38% not 0.35%.

[tamerj1]

@arquironsalud 
What is the DAX fro both SALES and COSTS?

[arquironsalud · Accepted solution]

Hello,
Thanks for your point, it has led me to find a solution. There is a slight difference. The formula that worked for me was:

PESO =
DIVIDE ( [COSTS], CALCULATE ( [SALES], ALLSELECTED () ) )

[arquironsalud]

Hi, the relationship between both tables is ACCOUNTS[15 DIG] with BALANCES[ACCOUNT 15], not "1 DIG". Thanks

[andhiii079845]

My fault sorry! But in your matrix you use "1 DIG" as a column. Can you remove it? 

[andhiii079845]

The problem is your data model in my opinion. You use "1 DIG" for the relationship and also as a filter criteria in your DAX formula. You need a relationship via the "AGGREGATOR RATIOS". Than you are able to calculated for each RATIOS the correct Costs and sales. Perhaps you have to transform your data. Perhaps some sample data will help to understand the data structure.