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.
Hi Everyone,
Thank you for your time, Before getting into the problem directly I'll tell you the background of this.
I have the below data table, original table has more than 100K rows (this is a sample set of data).
Date | Customer | Category | Sold Qty | Reject Qty |
10/6/2018 | A | Cat_01 | 1000 | 100 |
10/6/2018 | A | Cat_01 | 1100 | 55 |
10/6/2018 | A | Cat_01 | 1250 | 63 |
10/7/2018 | A | Cat_01 | 950 | 95 |
10/7/2018 | A | Cat_01 | 800 | 80 |
10/7/2018 | A | Cat_01 | 500 | 50 |
10/8/2018 | A | Cat_01 | 1150 | 58 |
10/8/2018 | A | Cat_01 | 1250 | |
10/8/2018 | B | Cat_01 | 1300 | 65 |
10/9/2018 | B | Cat_02 | 2000 | 100 |
10/9/2018 | B | Cat_02 | 2100 | 105 |
10/9/2018 | B | Cat_02 | 2500 | 125 |
10/10/2018 | B | Cat_02 | 3500 | |
10/10/2018 | B | Cat_02 | 1000 | 100 |
10/11/2018 | B | Cat_02 | 1500 | 75 |
10/13/2018 | B | Cat_02 | 890 | 89 |
10/19/2018 | C | Cat_02 | 990 | 99 |
10/16/2018 | C | Cat_03 | 450 | 45 |
10/17/2018 | C | Cat_03 | 660 | 66 |
10/18/2018 | C | Cat_03 | 770 | 77 |
10/20/2018 | C | Cat_03 | 1800 | 90 |
10/20/2018 | C | Cat_03 | 2100 | 105 |
10/22/2018 | C | Cat_03 | 5200 | 260 |
10/22/2018 | C | Cat_03 | 3000 | 150 |
11/3/2018 | C | Cat_04 | 2200 | 110 |
11/4/2018 | C | Cat_04 | 1000 | 100 |
11/5/2018 | C | Cat_04 | 1100 | 55 |
11/5/2018 | D | Cat_04 | 1250 | 63 |
11/6/2018 | D | Cat_04 | 950 | 95 |
11/6/2018 | D | Cat_04 | 800 | 80 |
11/7/2018 | D | Cat_04 | 500 | |
11/7/2018 | D | Cat_05 | 1150 | 58 |
11/8/2018 | D | Cat_05 | 1250 | 63 |
11/10/2018 | D | Cat_05 | 1300 | 65 |
11/11/2018 | D | Cat_05 | 2000 | 100 |
11/12/2018 | D | Cat_05 | 2100 | 105 |
11/13/2018 | D | Cat_05 | 2500 | 125 |
11/13/2018 | D | Cat_05 | 3500 | |
11/14/2018 | D | Cat_05 | 1000 | 100 |
11/14/2018 | D | Cat_05 | 1500 | 75 |
11/15/2018 | D | Cat_05 | 890 | 89 |
11/15/2018 | D | Cat_05 | 990 | |
11/15/2018 | D | Cat_05 | 450 | 45 |
11/15/2018 | D | Cat_05 | 660 | 66 |
11/15/2018 | D | Cat_05 | 770 | 77 |
11/15/2018 | D | Cat_05 | 1800 | 90 |
11/17/2018 | D | Cat_05 | 2100 | 105 |
I want to calculate the "Good Sales %" and I used below function,
Good Sales % = VAR Rejects = SUM ( 'Sales Rejections'[Reject Qty] ) VAR Sales = SUM ( 'Sales Rejections'[Sold Qty] ) RETURN IFERROR ( ( IF(Rejects>=0,CALCULATE ((Sales - Rejects) / Sales, ALLSELECTED ( 'Sales Rejections' ) ), "") ) , "" )
And After this calculation I'm getting below result [which is what I want] (Category wise sales pass %). However, I also want to show the Net Sales %. I don't know how to get this number in power BI.
I tried PRODUCT function but couldn’t get what I want since it requires a calculated column not a measure. I think I'm missing something important here.
The result i'm expecting in this case is 75.08% (it is the multiplication of below highlighted values 93.91% * 95.21% * 94.33% * 93.55% * 95.15%)
Also, please note in this case there are 5 Categories, However, this is not static sometimes you may have only 2 or 3 categories.
I have a separate calendar table and date filters are from that table.
One View
Thanks again...
Solved! Go to Solution.
Hi @dilumd,
Please new an extra measure based on the existing [Good Sales %].
Net Sales % = PRODUCTX( VALUES('Sales Rejections'[Category]),[Good Sales %])
Best regards,
Yuliana Gu
Hi @dilumd,
Please new an extra measure based on the existing [Good Sales %].
Net Sales % = PRODUCTX( VALUES('Sales Rejections'[Category]),[Good Sales %])
Best regards,
Yuliana Gu
Hi @dilumd
If you prefer to have everything in just one measure you could discriminate between the Total row and the others with ISFILTERED(). You would thus use your code when ISFILTERED( 'Sales Rejections'[Category]) is TRUE and @v-yulgu-msft's code when ISFILTERED( 'Sales Rejections'[Category]) is FALSE (i.e., at the Total row). Something like:
NewMeasure= IF ( ISFILTERED( 'Sales Rejections'[Category]), Your code here, @v-yulgu-msft's code here )
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
78 | |
60 | |
51 |
User | Count |
---|---|
164 | |
84 | |
68 | |
68 | |
58 |