Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
arquironsalud
Regular Visitor

DIVIDE two tables measure

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.

 

arquironsalud_0-1677492660935.png

arquironsalud_1-1677492681893.png

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:

 

arquironsalud_2-1677492719865.png

 

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.

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

View solution in original post

7 REPLIES 7
tamerj1
Super User
Super User

@arquironsalud 

Please try

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

arquironsalud_0-1677507885904.png

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

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

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
Regular Visitor

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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




andhiii079845
Super User
Super User

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. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors