Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
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 Accept it as the solution to help the other members find it more quickly.
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 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:
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 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 😉
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.
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
7 |
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
11 |