FILTERED SUM Over Many Rows

[Anonymous]

Hello Everyone, 

I have tried many different approaches and nothing is working as of right now.  

I'm trying to automate a financial report for the accounting team. I'm importing information from SQL. 

My table looks something like this. 

accountnumber	last day of year	janbalance	febbalance	store #
100	12/31/2021	-1400	-1100	Store - A
153	12/31/2022	-2854	-9874	Store - B
201	12/31/2022	-741	-854	Store - B

While I wish dates were there own column to help with filtering. Im nervous on transforming the data due to this being money and second data validation is already an issue and I like to rule that out right away. However if this result needs transformation to help make the project easier I'm open to that solution. 

My desire is that it will sum the balance for the month based on the account and store conditions. 

Feb GP = CALCULATE( SUM(Query1[febbalance]),

Query1[accountnumber] = 153,

Query1[accountnumber] = 201,

Query1[accountnumber] = etc)

I tried this formula then use slicers as powerbi to slice year and store. This returns a blank result when there is information in the table. 

Feb GP = CALCULATE( SUM(Query1[febbalance]),

Query1[accountnumber] = 153 || 

Query1[accountnumber] = 201 || 

Query1[accountnumber] = etc && 

Query1[Store #] = "Store-B" && 

Query1[last day of year] = 12/31/2022 ) 

This result produced nothing. 

I do get results when its just || "OR" statements but then I cant filter the result any further. 

If my data was what it is above I should get  - $ 10, 728 

This is an imcomplete table screenshot of my end goal but I plan on summing all values in accounts and using a switch function to show the correct summation for each group. 

 Any advice and help is greatly appreciated. 

I have also tried using calculate and a filter function and that didnt work either. 

[rbriga]

I would first create two new dimension tables: Accounts and Stores.

Since you're using SQL, it should be something akin to 

SELECT DISTINCT accountnumber AS "Account Number" FROM TABLE

SELECT DISTINCT store# AS "Store #" FROM TABLE

Then, create a relationship between these tables and the appropriate columns in the fact table above.

Next, as you suspected, I would reccomend transforming the fact table.

• Rename the colums: janbalance = 1, febbalance = 2, etc.
• Unpivot the monthly balance columns. We'll call their numbers "Month" and their value "Balance".
• Create a date foreign key for each row.

In powerQuery, you can do something like:

= Table.AddColumn(#"Changed Type", "Year-Month", each Text.Combine({Text.From([Month], "en-US"), "/1/", DateTime.ToText([Last day of the year], "yyyy")}), type date)

• ​I'd create a calendar table with month, year, year-month, etc;.

Then, connect it to the "Year-Month" column we've just created.

Now you're set!

SUM the Balance column, while using the accounts, stores and calendar tables we created (instead of the columns in the fact table).

I hope it works!

[Anonymous]

First, thank you. Rewriting my SQL query and performing some transformations helped shape the data. So for my delayed appreciation as many other challenges come up during this project that required additional time.