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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Enrico501
Frequent Visitor

Measure to calculate Total Forecast by Each Country

DateCountryForecast
17/04/2023France2830
18/04/2023France2758
19/04/2023France2938
20/04/2023France3033
21/04/2023France2905
22/04/2023France2760
23/04/2023France2486
17/04/2023Netherlands418
18/04/2023Netherlands489
19/04/2023Netherlands500
20/04/2023Netherlands569
21/04/2023Netherlands586
22/04/2023Netherlands580
23/04/2023Netherlands573
17/04/2023Denmark28
18/04/2023Denmark47
19/04/2023Denmark34
20/04/2023Denmark35
21/04/2023Denmark36
22/04/2023Denmark34
23/04/2023Denmark27
17/04/2023Sweden128
18/04/2023Sweden164
19/04/2023Sweden199
20/04/2023Sweden187
21/04/2023Sweden169
22/04/2023Sweden170
23/04/2023Sweden164
17/04/2023Germany1064
18/04/2023Germany1258
19/04/2023Germany1310
20/04/2023Germany1330
21/04/2023Germany1266
22/04/2023Germany1217
23/04/2023Germany1092
17/04/2023Spain33
18/04/2023Spain37
19/04/2023Spain52
20/04/2023Spain50
21/04/2023Spain38
22/04/2023Spain45
23/04/2023Spain35
17/04/2023Italy48
18/04/2023Italy53
19/04/2023Italy61
20/04/2023Italy67
21/04/2023Italy67
22/04/2023Italy68
23/04/2023Italy59
17/04/2023Other296
18/04/2023Other365
19/04/2023Other388
20/04/2023Other395
21/04/2023Other369
22/04/2023Other364
23/04/2023Other297

Hi

How do I write a Meausre that will Calculate the Total Forecast by Each Country? 

 

I am Close but I can't get the filtering right. This is what I have so far.

 

Total Forecast = CALCULATE(SUM('Boohoo Forecast'[Forecast]), ALL('Boohoo Forecast'[Country]))
 
The result is This which is the total for all the countries combined rather than each country.
 
Enrico501_0-1684939023394.png

Any help would be appreciated.

 

 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @Enrico501 ,

By default, if you put Country and Forecast columns in one visual and select Sum of Forecast, it will automatically sum the values based on each country, this is called "context" in Power BI.

vyanjiangmsft_0-1685090452064.png

vyanjiangmsft_1-1685090536918.png

If you prefer a measure, here's the solution:

Measure =
SUMX (
    FILTER ( ALL ( 'Table' ), 'Table'[Country] = MAX ( 'Table'[Country] ) ),
    'Table'[Forecast]
)

I attach my sample below for your reference.

 

Best regards,

Community Support Team_yanjiang

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-yanjiang-msft
Community Support
Community Support

Hi @Enrico501 ,

By default, if you put Country and Forecast columns in one visual and select Sum of Forecast, it will automatically sum the values based on each country, this is called "context" in Power BI.

vyanjiangmsft_0-1685090452064.png

vyanjiangmsft_1-1685090536918.png

If you prefer a measure, here's the solution:

Measure =
SUMX (
    FILTER ( ALL ( 'Table' ), 'Table'[Country] = MAX ( 'Table'[Country] ) ),
    'Table'[Forecast]
)

I attach my sample below for your reference.

 

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

RuyiLuo
Regular Visitor

Total Forecast = SUM('Boohoo Forecast'[Forecast])

or

Total Forecast = CALCULATE(SUM('Boohoo Forecast'[Forecast]))

 

That totals the Forecast in total. It does not split it out in country. I need it to  Calculate the totals but Country

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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