Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dilumd
Solution Supplier
Solution Supplier

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

 

DateCustomerCategorySold QtyReject Qty
10/6/2018ACat_011000              100
10/6/2018ACat_011100                55
10/6/2018ACat_011250                63
10/7/2018ACat_01950                95
10/7/2018ACat_01800                80
10/7/2018ACat_01500                50
10/8/2018ACat_011150                58
10/8/2018ACat_011250 
10/8/2018BCat_011300                65
10/9/2018BCat_022000              100
10/9/2018BCat_022100              105
10/9/2018BCat_022500              125
10/10/2018BCat_023500 
10/10/2018BCat_021000              100
10/11/2018BCat_021500                75
10/13/2018BCat_02890                89
10/19/2018CCat_02990                99
10/16/2018CCat_03450                45
10/17/2018CCat_03660                66
10/18/2018CCat_03770                77
10/20/2018CCat_031800                90
10/20/2018CCat_032100              105
10/22/2018CCat_035200              260
10/22/2018CCat_033000              150
11/3/2018CCat_042200              110
11/4/2018CCat_041000              100
11/5/2018CCat_041100                55
11/5/2018DCat_041250                63
11/6/2018DCat_04950                95
11/6/2018DCat_04800                80
11/7/2018DCat_04500 
11/7/2018DCat_051150                58
11/8/2018DCat_051250                63
11/10/2018DCat_051300                65
11/11/2018DCat_052000              100
11/12/2018DCat_052100              105
11/13/2018DCat_052500              125
11/13/2018DCat_053500 
11/14/2018DCat_051000              100
11/14/2018DCat_051500                75
11/15/2018DCat_05890                89
11/15/2018DCat_05990 
11/15/2018DCat_05450                45
11/15/2018DCat_05660                66
11/15/2018DCat_05770                77
11/15/2018DCat_051800                90
11/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 ViewOne View

Thanks again...

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @dilumd,

 

Please new an extra measure based on the existing [Good Sales %].

Net Sales % = 
 PRODUCTX( VALUES('Sales Rejections'[Category]),[Good Sales %])

1.PNG2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @dilumd,

 

Please new an extra measure based on the existing [Good Sales %].

Net Sales % = 
 PRODUCTX( VALUES('Sales Rejections'[Category]),[Good Sales %])

1.PNG2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.