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). DateCustomerCategorySold QtyReject Qty10/6/2018ACat_011000 10010/6/2018ACat_011100 5510/6/2018ACat_011250 6310/7/2018ACat_01950 9510/7/2018ACat_01800 8010/7/2018ACat_01500 5010/8/2018ACat_011150 5810/8/2018ACat_011250 10/8/2018BCat_011300 6510/9/2018BCat_022000 10010/9/2018BCat_022100 10510/9/2018BCat_022500 12510/10/2018BCat_023500 10/10/2018BCat_021000 10010/11/2018BCat_021500 7510/13/2018BCat_02890 8910/19/2018CCat_02990 9910/16/2018CCat_03450 4510/17/2018CCat_03660 6610/18/2018CCat_03770 7710/20/2018CCat_031800 9010/20/2018CCat_032100 10510/22/2018CCat_035200 26010/22/2018CCat_033000 15011/3/2018CCat_042200 11011/4/2018CCat_041000 10011/5/2018CCat_041100 5511/5/2018DCat_041250 6311/6/2018DCat_04950 9511/6/2018DCat_04800 8011/7/2018DCat_04500 11/7/2018DCat_051150 5811/8/2018DCat_051250 6311/10/2018DCat_051300 6511/11/2018DCat_052000 10011/12/2018DCat_052100 10511/13/2018DCat_052500 12511/13/2018DCat_053500 11/14/2018DCat_051000 10011/14/2018DCat_051500 7511/15/2018DCat_05890 8911/15/2018DCat_05990 11/15/2018DCat_05450 4511/15/2018DCat_05660 6611/15/2018DCat_05770 7711/15/2018DCat_051800 9011/17/2018DCat_052100 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 ViewThanks again...

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

dilumd

Impactful Individual

7 years ago

Solved

Multiply rows in DAX

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

Need Help

v-yulgu-msft
7 years ago
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

3 Replies

Greg_Deckler
Community Champion
7 years ago
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
v-yulgu-msft
Microsoft Employee
7 years ago
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
  Community Champion
  7 years ago
  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 )

Forum Discussion

Multiply rows in DAX

3 Replies

Related content

Previous Row Cell multiplied with This Row Cell

Multiply rows

DAX Measure for Ranking the valid rows and calculating the average rating

DAX - Multiply by Explicit Row Values

DAX total != total rows