0

I am trying to figure out how to mimic a SQL partition in a DAX query. If I was using SQL I would use something similar to this: 

sum([Total Units]) over (partition by [Fiscal Month]) as ttl_mth_unit

or 

,Sum(Case when 'Order Line Item Details'[No of Transfers] = 1 then 'Order Line Item Details'[Total Units] end)) as single

,Single/ sum('Order Line Item Details'[Total Units]) as perct_single

My data currently looks like this: 

Fiscal Month    Transfer Cnt    Units 
2017-Apr            0            100
2017-Apr            1            300

Ideally the results would look like this:

Fiscal Month    0transfer   1transfer   %0     %1     ttl
2017-Apr       100           300        .25    .75    400

or this: 

Fiscal Mon th   Transfer Cnt    Units     %          ttl units
2017-Apr           0              100   0.25        400
2017-Apr            1             300   0.75        400

this is my DAX code

evaluate(
filter(
addcolumns(
summarize(
'Order Line Details'
,'Calendar'[Fiscal Month]
,'Calendar'[Fiscal Year Nbr]
,'Order Line Item Details'[No of Transfers]
,"Total Units Test",'Order Line Item Details'[Total Units]
),
"Month Abbr", Mid('Calendar'[Fiscal Month],1,3)
,"Month ID", 'Calendar'[Fiscal Year Nbr]&"-"&Mid('Calendar'[Fiscal Month],1,3)
),
 [Fiscal Year Nbr]>(2015)
)
)

I've tried using summarize and different variations of sumx but I am either using the wrong functions or not setting it up properly.

symn28
  • 13
  • 1
  • 5

1 Answers1

0
CALCULATE([Total],DATESMTD(Date[calendarDate]))

Do not forget to mark your date table as date table in Tabular model, otherwise this will not wok. It got me before.

Dariusz Bielak
  • 415
  • 2
  • 7
  • Thank you! I was able to covert the rows to columns using this : ,"0 Quantity",calculate(sum('Order Line Item Details'[Quantity]),'Order Line Item Details'[No of Transfers]=0) – symn28 Apr 20 '16 at 19:10
  • I will try using the calculate function. – symn28 Apr 20 '16 at 19:13
  • Not to worry, could you mark the question as answered, if this answered your query. I found this blog very informative, not only for the DAX but SSAS in general, hope you also will find it useful https://blog.crossjoin.co.uk/category/dax/ – Dariusz Bielak Apr 20 '16 at 19:19