Skip to main content
cancel
Showing results for 
Search instead 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

Reply
RedaBi
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.

 

RedaBi_0-1712956542777.png

 

1 ACCEPTED SOLUTION

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:

vjianpengmsft_0-1713230210703.png

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

vjianpengmsft_6-1713232482175.png

 

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:

vjianpengmsft_3-1713230549261.png

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:

vjianpengmsft_7-1713233873622.png

 

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,

vjianpengmsft_5-1713231055147.png

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

 

 

 

View solution in original post

6 REPLIES 6
v-jianpeng-msft
Community Support
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:

vjianpengmsft_0-1713158957763.png

sales table:

vjianpengmsft_5-1713160376567.png

 

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

vjianpengmsft_2-1713159673614.png

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:

vjianpengmsft_3-1713159827162.png

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:

vjianpengmsft_4-1713160024424.png

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

 

 

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

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:

vjianpengmsft_0-1713230210703.png

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

vjianpengmsft_6-1713232482175.png

 

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:

vjianpengmsft_3-1713230549261.png

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:

vjianpengmsft_7-1713233873622.png

 

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,

vjianpengmsft_5-1713231055147.png

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

 

 

 

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 😉

lbendlin
Super User
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.

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

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

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.

PBI_Carousel_NL_June

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.