powerpivot - DAX partition/monthly totals -


i trying figure out how mimic sql partition in dax query. if using sql use similar this:

sum([total units]) on (partition [fiscal month]) ttl_mth_unit 

or

,sum(case when 'order line item details'[no of transfers] = 1 'order line item details'[total units] end)) single  ,single/ sum('order line item details'[total units]) perct_single 

my data looks this:

fiscal month    transfer cnt    units  2017-apr            0            100 2017-apr            1            300 

ideally results 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 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 , different variations of sumx either using wrong functions or not setting properly.

calculate([total],datesmtd(date[calendardate]))  

do not forget mark date table date table in tabular model, otherwise not wok. got me before.


Comments

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

asp.net mvc - breakpoint on javascript in CSHTML? -