topic Re: How to calculate one measurevalue against other in DAX Commands and Tips

How to calculate one measurevalue against other

Anonymous — Thu, 22 Apr 2021 17:44:43 GMT

Hi,

I have following table:

Costs	Ist_Value
Revenue	2345
Material Costs	117
Salary	189
Rent	378

I want to calculate ig. % of Material Cost based on Revenue

= ist_value / Revenue

My result should be following:

Costs	Ist_Value	%Value
Revenue	2345	100%
Material Costs	117	4,90%
Salary	189	8,06%
Rent	378	16,12%

I try to extract the value for Cost=Revenue and use it for further calculation

Revenue =

CALCULATE(

SUMX(Table1,

Table1[Ist_Value]),

Table1[Costs] = "Revenue")

% Value =

DIVIDE(SUM(Table1[Ist_Value]),[Revenue])

But its always just showing % for Revenue:

Costs	Ist_Value	Revenue	% Value
Revenue	2345	2345	100%
Material Costs	117
Salary	189
Rent	378

What am I doing wrong?

Re: How to calculate one measurevalue against other

Jihwan_Kim — Thu, 22 Apr 2021 18:04:36 GMT

Hi, Anonymous

I think it is related to your Revenue measure.

Please try to replace it to the below.

Revenue =
CALCULATE (
SUMX ( 'Table', 'Table'[Ist_Value] ),
FILTER ( ALL ( 'Table' ), 'Table'[Costs] = "Revenue" )
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Re: How to calculate one measurevalue against other

Anonymous — Thu, 22 Apr 2021 19:37:57 GMT

Hi Jihwan Kim,

thanks for your reply.

It's almost correct.

Unfortunatly I wasn't 100% clear.

My original Table has also additionally a date inside:

(arggh I can't past Table)
Date Costs Ist_value

Jan Revenue 2345

Jan Material C 238

Feb Revenue 5689

Feb Material C 245

How can I still consider the date within Revenue calculation?

Right now with your approach it's taking total Sum of Revenue over whole table but it should be still grouped by date.

(sorry for not being accurate enough at the begining)

Re: How to calculate one measurevalue against other

Jihwan_Kim — Fri, 23 Apr 2021 02:58:16 GMT

Hi, Anonymous

In that case, the situation has been changed to me.

So, I need to write the measure again from the beginning.

Please try the below and let me know whether it works.

Revenue =
CALCULATE (
SUMX ( 'Table', 'Table'[Ist_Value] ),
FILTER ( ALL ( 'Table' [Cost]), 'Table'[Costs] = "Revenue" )
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Re: How to calculate one measurevalue against other

Anonymous — Fri, 23 Apr 2021 08:18:06 GMT

Hi Jihwan_Kim ,

thanks for your help!

Its working.