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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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