cancel
Showing results for
Did you mean:

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

Frequent Visitor

## Measure to Calculate Objective

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.

1 ACCEPTED SOLUTION
Community Support

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.

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 Accept it as the solution to help the other members find it more quickly.

6 REPLIES 6
Community Support

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.

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 Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

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."

Community Support

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.

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 Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

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 😉

Super User

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.

Frequent Visitor

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

Announcements

#### 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 Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors