Help with understanding DAX table expressions and calculations

[joemac130]

All -

Forgive me in advance if the answer to this question is overly simplistic... New to DAX...

I have 2 queries that I have structured and neither is generating the output that I desire / anticipate...

I do believe and understand that the queries are generating the output as they are constructed and I know that the issue is that I'm just not structuring the query to provide me with the desired output, but I'm 'struggling' to understand how to establish the baseline query to produce the desired output. Please provide guidance to a very eager student...

Below are the 2 queries as formatted in Dax Editor (Dax Formatter) - I have masked the naming conventions and number values as appropriate for the privacy of the data...

EVALUATE
CALCULATETABLE (
ADDCOLUMNS (
ADDCOLUMNS (
CALCULATETABLE (
ADDCOLUMNS (
CALCULATETABLE ( DISTINCT ( 'ACS'[ACCT] ) ),
"JOE_CNT_COLUMN", COUNTX ( 'ACS', 'ACS'[SOLD] )
)
),
"JOE_SUM_COLUMN", SUM ( 'ACS'[SOLD] )
),
"JOE_SUMX_Column", ROW (
"JOE ACCT", SUMX ( 'ACS', 'ACS'[SOLD] )
)
),
FILTER (
'ACS',
YEAR ( 'ACS'[Dep_Date] ) = 2016
),
FILTER (
'ACS',
MONTH ( 'ACS'[Dep_Date] ) = 2
),
FILTER (
'ACS',
'ACS'[ACCT] = "12345"
|| 'ACS'[ACCT] = "54321"
)
)
ORDER BY 'ACS'[ACCT] ASC
This output as generated by the query is showing me a line for each ACCT however the aggregations are just that, the total of all rows and the sum of all values for the 2 ACCTs
//
//ACCT    JOE_CNT_COLUMN JOE_SUM_COLUMN JOE_SUMX_Column
//54321   9                              12                             12
//12345   9                              12                             12

The DESIRED output from the query would show a line for each ACCT and the individual buckets for each account without the aggregation
//
//ACCT    JOE_CNT_COLUMN JOE_SUM_COLUMN JOE_SUMX_Column
//54321   1                              12                              0.5
//12345   8                              12                              11.5

EVALUATE
CALCULATETABLE (
ADDCOLUMNS (
CALCULATETABLE (
DISTINCT ( 'ACS'[ACCT] ),
FILTER (
'ACS',
'ACS'[ACCT] = "12345"
|| 'ACS'[ACCT] = "54321"
),
FILTER (
'ACS',
YEAR ( 'ACS'[Dep_Date] ) = 2016
),
FILTER (
'ACS',
MONTH ( 'ACS'[Dep_Date] ) = 2
)
),
"JOE_COUNTX", COUNTX ( 'ACS', 'ACS'[SOLD] )
)
)

This output as generated by the query is showing me a line for each ACCT however the aggregations are just that, the total of all rows and (not just these 2 ACCT)

//ACCT      JOE_COUNTX
//54321    24144648
//12345    24144648

The DESIRED output from the query would show a line for each ACCT and the individual buckets for each account without the aggregation

//ACCT    JOE_COUNTX
//54321   1
//12345   8

[GilbertQ]

Hi @joemac130

You did not indicate what measure you are trying to calculate?

As well as if you have some sample data? As it currently appears that you are trying to do do everything via a calculated table, which possibly could be achieved in the Query Editor or using Calculated measures.

[joemac130]

Thank you for the review.  I went back to 'basics' (even further than in the request for help...)
Using a 'baby-step' approach I was able to back into the numbers.  I found a series of Youtube videos as well as some great videos on https://projectbotticelli.com/ (some free and some subscription based) that have assisted me as well.

This truly is a different way of thinking.