DAX Query for Revenue Aggregation

[Anonymous]

 Can someone help me to get the DAX equivalent for the below : 
select count(distinct DW_ACCT_ID) #ACCT,
MKT_REGION_CALC,
[MKT_PROD_LVL_2_NM],
[MKT_PROD_LVL_2_NM],
[COUNTRY],
[FISC_QTR_RLTV],
[FISC_WEEK_RLTV],
sum([REVN_AS_SLD_DISC_USD]) REV
from
[dbo].[vw_Dell_Order_Details]
where MKT_REGION_CALC = 'India' and [FISC_QTR_RLTV] = '-1'
Group by
MKT_REGION_CALC,
[MKT_PROD_LVL_2_NM],
[MKT_PROD_LVL_2_NM],
[COUNTRY],
[FISC_QTR_RLTV],
[FISC_WEEK_RLTV]
having sum([REVN_AS_SLD_DISC_USD]) > 1000000

DAX problem sum average 

[v-qiuyu-msft]

Hi @Anonymous, 

The T-SQL query returns a table data. Do you want to create a new table in Power BI desktop? If it is, the DAX will be complicate. I would suggest you using Get Data-> SQL database, then write the T-SQL query to get the desired table. 

If you want to create a column or measure, please share some sample data of vw_Dell_Order_Details table and clarify the desired output. 

Best Regards,
Qiuyun Yu

[Anonymous]

I am not sure how to convert the Having clause in SQL to be added into Dax . Below is the expected output. I extacted data from Cube. Below is the DAX query. Can you just add the having clause in the below dax.

#Acct	MKT_REGION_CALC	MLT_PROD_LVL	Country	FISC_QTR	FISCAL_WEEK	Rev
10	ANZ	Server	Australia	-1	12	$     1,100,000
2	INDIA	Server	India	-1	12	$     4,000,000
4	JAPAN	Server	Japan	-1	12	$     6,000,000

EVALUATE

( SUMMARIZECOLUMNS
(
'Management Go To Market Hierarchy'[Sales Business Unit Level 1],
'Management Go To Market Hierarchy'[Sales Business Unit Level 2],
'Management Go To Market Hierarchy'[Sales Business Unit Level 3],
'Management Go To Market Hierarchy'[Sales Business Unit Level 4],
'Management Product Hierarchy'[Product Level 1],
'Management Product Hierarchy'[Product Level 2],
'Management Product Hierarchy'[Product Level 3],
'Management Product Hierarchy'[Product Level 4],
'Management Product Hierarchy'[Product Level 5],
'Management Product Hierarchy'[Product Level 6],
'Fiscal Calendar'[Quarter Aging],
'Fiscal Calendar'[Quarter],
'Fiscal Calendar'[Quarter Week Num],
'Fiscal Calendar'[Quarter Week Aging],
'Management Product Hierarchy'[Type],
'Management Product Hierarchy'[Group],
'Account - End User'[EU Sub Account ID],
// 'Account - End User'[EU Org Account ID] ,


FILTER
(
VALUES('Management Go To Market Hierarchy'[Sales Business Unit Level 2]), ('Management Go To Market Hierarchy'[Sales Business Unit Level 2] = "APJ CSES")


),




KEEPFILTERS(
FILTER(
KEEPFILTERS(VALUES('Orders'[Order Type Code])),
'Orders'[Order Type Code] = "I"||
'Orders'[Order Type Code] = "C"
)
),

KEEPFILTERS(
FILTER(
KEEPFILTERS(VALUES('Finance Reporting Flag'[Finance Reporting Flag])),
'Finance Reporting Flag'[Finance Reporting Flag] = "Yes"
)
),


KEEPFILTERS(
FILTER(
KEEPFILTERS(values('Fiscal Calendar'[Quarter Aging])),
'Fiscal Calendar'[Quarter Aging] in {0,-1,-2}
)
)

//"Distinct count", Calculate(Distinct count('Account - End User'[EU Sub Account ID]), calculate(SUM('Metrics'[Total Rev Disc USD]) =>1000000))

//"Distinct Accounts", CALCULATE()

,"Revenue", 'Metrics'[Total Rev Disc USD]


))

@report 

[v-qiuyu-msft]

Hi @Anonymous, 

You can just filter the result of SUMMARIZECOLUMNS() function results by adding FILTER() function, see: https://www.sqlbi.com/articles/from-sql-to-dax-grouping-data/

Best Regards,
Qiuyun Yu