cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cramos
Frequent Visitor

Sum Total vs a single value

Hi,

 

I'm stuck with something very silly but I'cant find the solution.

 

I have the sales for the month and a target for each month. 

 

Something like this:

 

Table 1

 

MONTHTarget
Ene1200
Feb1345
Mar789
Abr1500
May

1600

 

Table 2

 

 SalesTarget
January SHOP 11281200
January SHOP 22351200
January SHOP 35241200
January SHOP 45761200

 

 

What I need is to calculated the total sales of all the shops vs the target of the moth that is 1200.  Solution expected 1463-1200=263. Which will be the optimum method to do this?

 

 

 

Thanks!!

 

 

1 ACCEPTED SOLUTION

Your tables have to be linked someway, for example, by month (Table1 to Table 2, 1 to many)

 

Then a new measure:

Sales diff = CALCULATE(SUM('Table 2'[Sales]))-CALCULATE(SUM('Table 1'[Target]))

 

Then you'll get desired amount for all months or for selected month only.

For example, if you make a bar chart visual with month number as x-axis, and put this measure in the values, you'll get what you want

Maxim Zelensky
excel-inside.pro

View solution in original post

4 REPLIES 4

Are the months have different names in your tables? "Ene" and "January"?

Maxim Zelensky
excel-inside.pro

Always the same, in fact i'm using the number of the month

Your tables have to be linked someway, for example, by month (Table1 to Table 2, 1 to many)

 

Then a new measure:

Sales diff = CALCULATE(SUM('Table 2'[Sales]))-CALCULATE(SUM('Table 1'[Target]))

 

Then you'll get desired amount for all months or for selected month only.

For example, if you make a bar chart visual with month number as x-axis, and put this measure in the values, you'll get what you want

Maxim Zelensky
excel-inside.pro

Works perfectly! Thank you so much!!

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors