Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to 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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
73 | |
64 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |