The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.

**2-for-1 sale on June 20 only!**

- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Re: Measure to Calculate Objective

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Measure to Calculate Objective

04-12-2024
02:16 PM

Using DAX, I want to create a measure to calculate the level reached based on the amount of sales by the seller, month, product, and year. It searches the objectives table based on the seller, month, and year, then returns the corresponding level, considering that I have intermediate tables such as the calendar table and the table of seller name, I will use something like (if sales >= palier 1 & sales > palier 2, 1).

Below are examples of the tables.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-15-2024
07:20 PM

Hi, @RedaBi

Thank you very much for your reply. First, I've created a calculated column in the sales table to find the month of the current date as follows:

At this point, put the month column and the Distrbuteur, YEAR column in the objectif table into the table visual:

I created a Sum CA measure using the following DAX expression:

```
Sum CA =
VAR _cd_dist =
SELECTEDVALUE ( objectif[Distrbuteur] )
RETURN
CALCULATE (
SUM ( 'sales'[CA] ),
TREATAS (
SELECTCOLUMNS ( 'objectif', "cd dist", 'objectif'[Distrbuteur] ),
sales[Cd Dist]
),
'sales'[Cd Dist] = _cd_dist
)
```

Note that there is no relationship established between my two tables:

I created a PALIER measure using the following DAX expression:

```
PALIER =
VAR _current_month =
SELECTEDVALUE ( 'sales'[Month] )
VAR _current_palier1 =
CALCULATE ( MAX ( 'objectif'[PALIER 1] ), 'objectif'[MONTH] = _current_month )
VAR _current_palier2 =
CALCULATE ( MAX ( 'objectif'[PALIER 2] ), 'objectif'[MONTH] = _current_month )
VAR _current_palier3 =
CALCULATE ( MAX ( 'objectif'[PALIER 3] ), 'objectif'[MONTH] = _current_month )
VAR _current_palier4 =
CALCULATE ( MAX ( 'objectif'[PALIER 4] ), 'objectif'[MONTH] = _current_month )
VAR _current_palier5 =
CALCULATE ( MAX ( 'objectif'[PALIER 5] ), 'objectif'[MONTH] = _current_month )
RETURN
IF (
[Sum CA] <> BLANK ()
&& _current_month
= CALCULATE ( MAX ( 'objectif'[MONTH] ), 'objectif'[MONTH] = _current_month ),
SWITCH (
TRUE (),
[Sum CA] >= _current_palier5, "PALIER 5",
[Sum CA] >= _current_palier4
&& [Sum CA] < _current_palier5, "PALIER 4",
[Sum CA] >= _current_palier3
&& [Sum CA] < _current_palier4, "PALIER 3",
[Sum CA] >= _current_palier2
&& [Sum CA] < _current_palier3, "PALIER 2",
[Sum CA] >= _current_palier1
&& [Sum CA] < _current_palier2, "PALIER 1"
)
)
```

Put these two measures into the table visual:

In the example you gave, D11 has a Sum CA of 1,000,000 in March 2024. Sum CA = 82000000 + 138719422 + 145000000 = 365719422 in January 2024.

Since in March 2024, the Sum CA of D11 is 1000000, according to the conditions you gave, it does not meet any of the grades in the objectif table, as shown in the figure below,

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post ** helps**, then please consider

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-14-2024
10:53 PM

Thanks @lbendlin .

Hi, @RedaBi

I have the following thoughts:

Based on the data provided in your image, and the expected results, I used the sample data as follows:

objectif table:

sales table:

First, extract the month of the current date column in the sales table:

Sum CA is calculated using the following DAX expression:

```
Sum CA =
VAR _current_cd_dist =
SELECTEDVALUE ( objectif[Distrbuteur] )
RETURN
CALCULATE (
SUM ( 'sales'[CA] ),
FILTER ( 'sales', 'sales'[Cd Dist] = _current_cd_dist )
)
```

Here are the results:

The PALIER is calculated using the following DAX expression:

```
PALIER =
VAR _current_month =
SELECTEDVALUE ( 'sales'[Month] )
VAR _current_palier1 =
CALCULATE ( MAX ( 'objectif'[PALIER 1] ), 'objectif'[MONTH] = _current_month )
VAR _current_palier2 =
CALCULATE ( MAX ( 'objectif'[PALIER 2] ), 'objectif'[MONTH] = _current_month )
VAR _current_palier3 =
CALCULATE ( MAX ( 'objectif'[PALIER 3] ), 'objectif'[MONTH] = _current_month )
VAR _current_palier4 =
CALCULATE ( MAX ( 'objectif'[PALIER 4] ), 'objectif'[MONTH] = _current_month )
VAR _current_palier5 =
CALCULATE ( MAX ( 'objectif'[PALIER 5] ), 'objectif'[MONTH] = _current_month )
RETURN
IF (
[Sum CA] <> BLANK (),
SWITCH (
TRUE (),
[Sum CA] >= _current_palier1, "PALIER 1",
[Sum CA] >= _current_palier2, "PALIER 2",
[Sum CA] >= _current_palier3, "PALIER 3",
[Sum CA] >= _current_palier4, "PALIER 4",
[Sum CA] >= _current_palier5, "PALIER 5"
)
)
```

Here are the results:

D11 has a sales of 1000 in March, and the PALIER should be PALIER 2. I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post ** helps**, then please consider

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-15-2024
01:36 PM

Thank you for your feedback and effort. It's almost there, but it didn't work. What I want is to calculate the sum of "ca" by month, year, and distributor, and compare them with the target lines according to month, year, and distributor with the following condition.

`If [Sum CA] >= _current_palier5 , "PALIER 5", if [Sum CA] >= _current_palier4 and [Sum CA] < _current_palier5, "PALIER 4", if [Sum CA] >= _current_palier3 and [Sum CA] < _current_palier4, "PALIER 3", if [Sum CA] >= _current_palier2 and [Sum CA] < _current_palier3, "PALIER 2", if [Sum CA] >= _current_palier1 and [Sum CA] < _current_palier2, "PALIER 1"`

"I created a .pbix file to clearly explain my problem, but I can't attach it."

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-15-2024
07:20 PM

Hi, @RedaBi

Thank you very much for your reply. First, I've created a calculated column in the sales table to find the month of the current date as follows:

At this point, put the month column and the Distrbuteur, YEAR column in the objectif table into the table visual:

I created a Sum CA measure using the following DAX expression:

```
Sum CA =
VAR _cd_dist =
SELECTEDVALUE ( objectif[Distrbuteur] )
RETURN
CALCULATE (
SUM ( 'sales'[CA] ),
TREATAS (
SELECTCOLUMNS ( 'objectif', "cd dist", 'objectif'[Distrbuteur] ),
sales[Cd Dist]
),
'sales'[Cd Dist] = _cd_dist
)
```

Note that there is no relationship established between my two tables:

I created a PALIER measure using the following DAX expression:

```
PALIER =
VAR _current_month =
SELECTEDVALUE ( 'sales'[Month] )
VAR _current_palier1 =
CALCULATE ( MAX ( 'objectif'[PALIER 1] ), 'objectif'[MONTH] = _current_month )
VAR _current_palier2 =
CALCULATE ( MAX ( 'objectif'[PALIER 2] ), 'objectif'[MONTH] = _current_month )
VAR _current_palier3 =
CALCULATE ( MAX ( 'objectif'[PALIER 3] ), 'objectif'[MONTH] = _current_month )
VAR _current_palier4 =
CALCULATE ( MAX ( 'objectif'[PALIER 4] ), 'objectif'[MONTH] = _current_month )
VAR _current_palier5 =
CALCULATE ( MAX ( 'objectif'[PALIER 5] ), 'objectif'[MONTH] = _current_month )
RETURN
IF (
[Sum CA] <> BLANK ()
&& _current_month
= CALCULATE ( MAX ( 'objectif'[MONTH] ), 'objectif'[MONTH] = _current_month ),
SWITCH (
TRUE (),
[Sum CA] >= _current_palier5, "PALIER 5",
[Sum CA] >= _current_palier4
&& [Sum CA] < _current_palier5, "PALIER 4",
[Sum CA] >= _current_palier3
&& [Sum CA] < _current_palier4, "PALIER 3",
[Sum CA] >= _current_palier2
&& [Sum CA] < _current_palier3, "PALIER 2",
[Sum CA] >= _current_palier1
&& [Sum CA] < _current_palier2, "PALIER 1"
)
)
```

Put these two measures into the table visual:

In the example you gave, D11 has a Sum CA of 1,000,000 in March 2024. Sum CA = 82000000 + 138719422 + 145000000 = 365719422 in January 2024.

Since in March 2024, the Sum CA of D11 is 1000000, according to the conditions you gave, it does not meet any of the grades in the objectif table, as shown in the figure below,

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post ** helps**, then please consider

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-16-2024
11:17 AM

It worked, thank you so much. I will try to adapt your measurement to my tables.

P.S.: Even artificial intelligence couldn't find a correct answer to my problem (it's reassuring in a way 😉

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-13-2024
09:56 AM

Please provide sample data that covers your issue or question **completely**, in a **usable** format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-15-2024
06:42 AM

Good evening, okay, I understand. I will be more careful in my future posts.

Announcements

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

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

Top Solution Authors

User | Count |
---|---|

63 | |

32 | |

21 | |

16 | |

15 |

Top Kudoed Authors

User | Count |
---|---|

115 | |

33 | |

30 | |

24 | |

21 |