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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.