Forum Discussion

powerbidev123's avatar
powerbidev123
Solution Sage
27 days ago
Solved

DAX help

I have a sales table with the following columns:

  • Sales[OrderDate]
  • Sales[Product]
  • Sales[Region]
  • Sales[Revenue]
  • Sales[Cost]

A date table is properly related to Sales[OrderDate].

I need help with DAX measures that returns:

  1. The product with the highest year-to-date revenue in the current filter context
  2. But only if that product’s gross margin % is above the overall company gross margin % for the same YTD period
  3. If no product meets that condition, return blank
  4. The measure must still work correctly when the report is filtered by Region, Year, and Month

Any kind of help will be appreciated!

Need Help
  • tharunkumarRTK's avatar
    tharunkumarRTK
    27 days ago

    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!
  • divyed's avatar
    divyed
    27 days ago

    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

     

2 Replies

  • tharunkumarRTK's avatar
    tharunkumarRTK
    Super User
    27 days ago

    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!
  • divyed's avatar
    divyed
    Super User
    27 days ago

    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