DAX help

powerbidev123 

You an follow this pattern

Revenue = SUM(Sales[Revenue])
Cost = SUM(Sales[Cost])

Gross Margin = 
Var __Revenue = [Revenue] 
Var __Cost = [Cost] 
Var __Result = Divide([Revenue] - [Cost] , [Cost])
Return __Result 


YTD Revenue = 
Calculate([Revenue], DATEYTD(Calendar[Date]))


ProductWithHighestYTDRevenue = 
Var __TOPN = TOPN(1, ALLSELECTED(Sales[Product]), [YTD Revenue], DESC, Sales[Product], ASC)
Var __ProductGrossMargin = Calculate([Gross Margin], KEEPFILTERS( __TOPN) )
Var __OverallGrossMargin = [Gross Margin]
Var __Result = if(__ProductGrossMargin > __OverallGrossMargin, MAXX(__TOPN, Sales[Product]))
Return __Result

Connect on LinkedIn

Read my blogs here: https://www.techietips.co.in/

Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

Hello powerbidev123 ,

You can use below dax :

Qualified Product YTD =
VAR
CompanyYTDRevenue = TOTALYTD(SUM(Sales[Revenue]), 'Date'[Date])
CompanyYTDCost = TOTALYTD(SUM(Sales[Cost]), 'Date'[Date])
CompanyYTDProfit = CompanyYTDRevenue - CompanyYTDCost
CompanyYTDMargin = DIVIDE(CompanyYTDProfit, CompanyYTDRevenue)

VAR ProductYTDTable =
ADDCOLUMNS(
ALLSELECTED(Sales[Product]),
"_ProdYTDRevenue", TOTALYTD(SUM(Sales[Revenue]), 'Date'[Date]),
"_ProdYTDCost", TOTALYTD(SUM(Sales[Cost]), 'Date'[Date])
)

VAR QualifiedProducts =
FILTER(
ADDCOLUMNS(
ProductYTDTable,
"_ProdYTDMargin", DIVIDE([_ProdYTDRevenue] - [_ProdYTDCost], [_ProdYTDRevenue])
),
[_ProdYTDMargin] > CompanyYTDMargin && [_ProdYTDRevenue] > 0
)

VAR TopProduct =
TOPN(
1,
QualifiedProducts,
[_ProdYTDRevenue],
DESC
)

RETURN SELECTCOLUMNS(TopProduct, "Product", Sales[Product])

You can modify slightly as per requirement.

I hope this helps.

Did I answer your query ? Mark this as solution if this solves your problem, Kudos are appreciated.

Cheers.

Neeraj Kumar