Multiply rows in DAX

[dilumd]

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...

[v-yulgu-msft · Accepted 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

[AlB]

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
)

[Greg_Deckler]

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