Need a working DAX on counting weeks

[mgd14work]

I am trying to figure out how to count the number of weeks where the price (TotalSales/TotalUnit) is below the average overall in respect to its year and banner. 

This is what I have and it is giving me blanks as an answer.

Promoted Week Count =

VAR TotalSales = CALCULATE(SUM('RSI Acct Merge'[Total Sales Amount]))

VAR TotalUnit = CALCULATE(SUM('RSI Acct Merge'[Total Sales Volume Units]))

VAR Avgprice = DIVIDE(TotalSales, TotalUnit)

VAR OverallAvgPrice = calculate(divide(sum('RSI Acct Merge'[Total Sales Amount]),sum('RSI Acct Merge'[Total Sales Volume Units])),ALLSELECTED('RSI Acct Merge'),values(AdWks[Year]),values('RSI Acct Merge'[BANNER NAME]))

RETURN

    CALCULATE(countrows(AdWks),filter(AdWks,Avgprice<OverallAvgPrice))

[dharmendars007 · Accepted solution]

Hello @mgd14work , 

Make sure that TotalUnit is not zero, as dividing by zero would return a blank result. You can safeguard this by adding a condition to avoid division by zero.

I have addedd the condition in the DAX if try this below..

Promoted Week Count =
VAR TotalSales = CALCULATE(SUM('RSI Acct Merge'[Total Sales Amount]))
VAR TotalUnit = CALCULATE(SUM('RSI Acct Merge'[Total Sales Volume Units]))
VAR Avgprice = IF(TotalUnit <> 0, DIVIDE(TotalSales, TotalUnit), BLANK())
VAR OverallAvgPrice = CALCULATE(
DIVIDE(SUM('RSI Acct Merge'[Total Sales Amount]), SUM('RSI Acct Merge'[Total Sales Volume Units])),
ALLSELECTED('RSI Acct Merge'),
VALUES(AdWks[Year]),VALUES('RSI Acct Merge'[BANNER NAME]))
RETURN
CALCULATE(COUNTROWS(AdWks),FILTER(AdWks, Avgprice < OverallAvgPrice))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN