cancel
Showing results for 
Search instead for 
Did you mean: 

DAX Query for Revenue Aggregation

 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 

Status: New
Comments
Moderator

Hi @arun_R2

 

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

Frequent Visitor

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.

 

#AcctMKT_REGION_CALCMLT_PROD_LVLCountryFISC_QTRFISCAL_WEEKRev
10ANZServerAustralia-112 $     1,100,000
2INDIAServerIndia-112 $     4,000,000
4JAPANServerJapan-112 $     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 

Moderator

Hi @arun_R2

 

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