Calculating Ratio between different levels in dimension table

[enestkm]

Hi, I am trying to calculate a ratio and could not find a solution so far, that would be great if you can help me out.

I have a fact table with more than million rows, and it is related to several dimension tables. One of the dimension tables is the group table which I want to use in my calculation.

I am trying to sum “cost for Group C” and “sales for Group D” and create a measure with dividing them into each other.

I can do it with calculated columns by using EARLIER function on the group dimension table however I need to have it as a measure and use it dynamically in my report. Creating multiple measures for every group is a solution but I believe there is a better way of doing it.

Eventually I am trying to show this ratio for every Group, furthermore being able drilling down through other dimension tables.

[v-lili6-msft]

hi, @enestkm 

If you could try this simple way:

Just use this formula to add a measure

Measure = DIVIDE(CALCULATE(SUM('fact'[Cost])),CALCULATE(SUM('fact'[Sales])),0)

Then drag group field from group dim table and this measure into a visual

(group dim table should create a relationship with fact table)

Best Regards,

Lin

[enestkm]

Hi @v-lili6-msft ,

Many thanks  for your reply.

That is the same relationship model that i have in my file , however what i am trying to do is a little different that you propose.

I need to divide the Cost values of Group C into SAles values of Group D. When i write the following code it does not work. I know it has something to do with row context but i could not find a way to solve it yet.

Measure = DIVIDE(

CALCULATE(SUM('fact'[Cost]), Group = "C" ),

CALCULATE(SUM('fact'[Sales]), Group = "D"),

0

) 

[v-lili6-msft]

hi, @enestkm 

What is the format of the expected output?

Is it not only Group column and Ratio

I know it has something to do with row context, so if you need to add ALLxxx functions in DAX.

If not your case, please share your the format of the expected output.

Best Regards,

Lin