topic Re: How to divide with a higher hierarchy group in DAX Commands and Tips

How to divide with a higher hierarchy group

ngutc — Wed, 14 Sep 2022 06:35:46 GMT

Hello,

I have these tables

Relationships

I want is to group values by state and get these results.

Sure, I can just put the column into the required fields. But I want to use these values for a calculation.

That would be Values in Facts Table 2 (F_state) divide with values in Facts table 1 group (F_city).

For example first line in Facts Table 2 > The value with the first line in Facts table 1 group value.

calculation = 985/21368

I can't figure out how to do that with a DAX calculation. How to include the higher hierarchy in the calculation. Or if it's possible even.

calc =

var state = Calculate(SUM(F_state[Value]))

var city = Calculate(SUM(F_city[Value]))

return Divide(state/city)

Thank you.

Re: How to divide with a higher hierarchy group

johnt75 — Wed, 14 Sep 2022 08:43:20 GMT

I can think of a couple of options. You could link your states dimension table to the cities dimension, creating a snowflake schema. Or within your measure you could move the filters between dimensions using TREATAS. For example, within an iterator over cities you could do something like

var totalStateValue = CALCULATE( SUM(F_state[value]), TREATAS( VALUES(D_cities[state]), D_states[state]))

Re: How to divide with a higher hierarchy group

ngutc — Wed, 14 Sep 2022 08:50:58 GMT

I can't make any changes in the model. That's why I'm trying to find out another way.

I dont really understad the function TREATAS, how does it work in this situation. is the var totalStateValue my defined var state? Why this facts table if I'm trying to make change in the F_city table?

Re: How to divide with a higher hierarchy group

johnt75 — Wed, 14 Sep 2022 09:23:29 GMT

TREATAS basically moves filters from one table to another. If you place a filter on the d_cities table for a specific city, e.g. in a table visual, that will also filter the values in the state column of d_cities table. You can then move that filter using TREATAS onto the d_states table, so any calculations you do on the F_state table will be filtered to just those rows related to the current state.

SQLBI have a video on TREATAS

Re: How to divide with a higher hierarchy group

ngutc — Wed, 14 Sep 2022 09:57:42 GMT

Could show on my example the use of this function?

Re: How to divide with a higher hierarchy group

johnt75 — Wed, 14 Sep 2022 10:35:34 GMT

Put the below measure in a table visual with F_city[state] and Time[Year]

My Measure = VAR cityValue = SUM ( F_city[Value] ) VAR stateValue = CALCULATE ( SUM ( F_state[Value] ), TREATAS ( VALUES ( F_city[state] ), d_state[state] ) ) RETURN DIVIDE ( stateValue, cityValue )

Re: How to divide with a higher hierarchy group

ngutc — Wed, 14 Sep 2022 12:30:00 GMT

In the line TREATAS -

( VALUES ( F_city[state] )

this value I don't have in my table (state column in not in the F_City table). How can it work¨,

Re: How to divide with a higher hierarchy group

johnt75 — Wed, 14 Sep 2022 12:48:02 GMT

use D_cities[state] instead

Re: How to divide with a higher hierarchy group

ngutc — Mon, 19 Sep 2022 11:01:54 GMT

I don't think that's the solution I am looking for. Or it doesn't give me the results I want. mainly I dont understand why do I treat as the column state in D_cities and D-state column state? And should't the treatas be in the filter of the first variable, the cityValue?