How to divide with a higher hierarchy group

[ngutc]

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. 

[johnt75]

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

[ngutc]

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? 

[johnt75]

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

[ngutc]

Could show on my example the use of this function?

[johnt75]

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 )

[ngutc]

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¨,

[johnt75]

use D_cities[state] instead

[ngutc]

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?