DAX Proportion of balance by state and date

user_3295746 0
Viewed 41

I am currently attempting to use DAX queries to calculate the proportion of the balance attributed to each State in my analysis cube, from the following image:

Initial data

I currently have a Sales table with a ReportDateKey that joins a ReportDate table that has a DateKey

If I use the following statement:

AllCurrentBalanceByDate:=CALCULATE([TotalCurrentBalance],ALLSELECTED())

It gives me the overall total, ignoring the date altogether, which is a useless figure.

If I enter the following query and display it in the excel spreadsheet:

AllCurrentBalanceByDate:=CALCULATE([TotalCurrentBalance],ALLSELECTED('Report Date'[Month]))

it is returning the same data as found in the Balance column. Again, useless. I need a total for each month, so that I can calculate the state balance / overall total for that month to get the proportion/percentage attributable to that State.

What am I doing wrong?

powerpivot dax ssas
1 Answers

So if you want your measure to ignore whichever State is selected, you need to include the State columns in your ALL filter.

Also I suppose you want to use ALL instead of ALLSELECTED as your overall balance per month shouldn't be affected by external filters on state (but this depends on your use case)?

AllCurrentBalanceByDate:=CALCULATE(SUM([CurrentBalance]),ALL(Geography[StateName]))
user_13090270
Related