topic Re: DIVIDE two tables measure in DAX Commands and Tips

DIVIDE two tables measure

arquironsalud — Mon, 27 Feb 2023 10:14:14 GMT

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.

Re: DIVIDE two tables measure

andhiii079845 — Mon, 27 Feb 2023 12:13:38 GMT

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.

Re: DIVIDE two tables measure

arquironsalud — Mon, 27 Feb 2023 12:17:22 GMT

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

Re: DIVIDE two tables measure

andhiii079845 — Mon, 27 Feb 2023 12:33:42 GMT

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

Re: DIVIDE two tables measure

tamerj1 — Mon, 27 Feb 2023 13:24:14 GMT

arquironsalud

Please try

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

Re: DIVIDE two tables measure

arquironsalud — Mon, 27 Feb 2023 14:27:55 GMT

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%.

Re: DIVIDE two tables measure

tamerj1 — Mon, 27 Feb 2023 15:14:39 GMT

arquironsalud
What is the DAX fro both SALES and COSTS?

Re: DIVIDE two tables measure

arquironsalud — Mon, 27 Feb 2023 16:04:24 GMT

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 () ) )