cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
calen
Helper I
Helper I

Help with Totaling

Hello All! 

 

I've got what I feel is a very simple problem but I can't quite figure out how to fix it. Below is a sample Dataset. I am trying to take the normal price - the discount price, then multiply that by the units sold to get the total discount. When I have it in a table, it works. When I put it in a card, the numbers go crazy. I am using the two measures below. What I want is a card that totals the "Total Discount" column which would be $6,765. What I am getting is $182,951 (the sum of discounts - $12 x total units - 15,246). Please help!

 

Unit Price - Discount =
SUM([Unit Price]) - AVERAGE([Discount Price])
 
Total Discount =
[Unit Price - Discount] * SUM([Quantity])

 

 

Item Average Unit Price Discount PriceUnit Price - Discount Quantity Total Discount
1 $                     15.00 $             14.00 $                         1.00               210 $              210
2 $                     20.00 $             19.00 $                         1.00            2,077 $           2,077
3 $                     19.00 $             18.00 $                         1.00            3,280 $           3,280
4 $                     10.00 $             10.00 $                              -              7,411 $                 -  
5 $                     23.00 $             22.00 $                         1.00                 35 $                35
6 $                     21.00 $             21.00 $                              -                 309 $                 -  
7 $                     24.00 $             23.00 $                         1.00                 85 $                85
8 $                     40.00 $             39.00 $                         1.00            1,003 $           1,003
9 $                     46.00 $             46.00 $                              -                 802 $                 -  
10 $                     66.00 $             62.00 $                         4.00                   4 $                15
11 $                     54.00 $             52.00 $                         2.00                 30 $                59
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@calen , I think it should be like

 

Discount Amount =

SUMX(Table, ([Unit Price] - [Discount Price])*[Quantity])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@calen , I think it should be like

 

Discount Amount =

SUMX(Table, ([Unit Price] - [Discount Price])*[Quantity])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

@amitchandak Thank you for your help! The unit price comes from the item table, the discount price comes from the pricing table table, and the quantity comes from the sales table.  Is there a way to bring all three tables into the measure above?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.