CODEWITHSUNDEEP
powerbidaxssas-tabular
Jim
Jim

Reputation: 11

need assistance\ideas on building what is hopefully a simple DAX measure

I am trying to figure out the DAX to create a new measure in an SSAS Tabular data model. An example of what I am trying to do is more easily shown than described. My SSAS Tabular dataset produces the following table. Cols A and B are from the stores table, Col C is a measure from the Sales table, Col D is a measure from the Products table, and Col E is C/D. This all works fine. Data has been mocked up in Excel to protect the innocent, but it is working in Power BI.

This is working

What I would like to do is add a new measure which calculates the Sales/Product at the state level and have that measure show for each store in that state, as shown below

This is what I want

Presumably I have to iterate over all rows and calculate the total sales/state and total products sold/state and divide those 2 to get the answer, but can't work out the DAX to get there. I have tried numerous combinations of

calculate(
    sumx(...),
    filter(
        all(...),
        ... 
    )
)

to no avail.

Upvotes: 0

Views: 50

Answers (2)

Jim
Jim

Reputation: 11

Thanks for the tip. I originally tried that and dropped it because I couldn't get it working. I revisited this morning and solved it. Here is what I did:

 State Ttl = 
     var trxYr = convert(SELECTEDVALUE(dim_date[Year]), INTEGER) //needed because Year is stored as text in the model
     var trxMo = SELECTEDVALUE(dim_Date[Short Month Name])
     var trxState = SELECTEDVALUE(fact_Sales[state])

 Return 
 CALCULATE(
    SUM(fact_sales[SalesAmt])
     ,all(fact_sales)
     ,year(fact_sales[SaleDATE]) = trxYr
     ,dim_Date[Short Month Name] = trxMo
     ,dim_Stores[state] = trxState
)

Upvotes: 0

msta42a
msta42a

Reputation: 3741

You should use FILTER with ALL to manipulate a context(remove current context);

MesureSumStateLevel = calculate(SUM('Table'[Amount]), FILTER(ALL('StoreStateTab'), 'StoreStateTab'[State] = SELECTEDVALUE('StoreStateTab'[State])))

https://dax.guide/filter/

https://dax.guide/selectedvalue/

https://dax.guide/all/

Upvotes: 0

Related Questions