- 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
Easy to fix! DAX formula
Hello,
the goal is to add 157$ (sector division=2 or Clients.secteur=20%...) in sector 01, 02 and 03. For the rest, let the normal amount and eliminate Clients.secteur=20%. The result should be for Clients.secteur= 01-Machine... : 293 290$ (293 132 + 147=293 290); for Clients.secteur= 02 - Marine... : 39 585$ and for Clients.secteur= 03 - Hydro... = 148 838$. The others Clients.secteur stay the same and the goal is eliminate Clients.secteur= 20%... it's why it's divided by 3 (471,5 / 3 = 157$)
Thanks!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ah, right. Try this:
TEST secteur 5 = IF(
Fusionner1[Division] = 1,
[Montant Measure] +
DIVIDE(
SUMX(
FILTER(REMOVEFILTERS(Fusionner1, [Clients.Secteur]), Fusionner1[Clients.Secteur ] = "20% MARINE, 20% HYDRO - 20% MACHINE - 40% PIPELINES"),
[Montant secteur]),
3),
[Montant Measure])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @sportive92003
Has your problem been solved? If so, please mark the corresponding answer as a solution. If not, please provide sample data that fully covers your issue(in the form of pbix file or table) and the expected outcome based on the sample data you provided.
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Assuming I'm understanding the assignment properly:
IF([Clients.secteur] in {"1", "2", "3"}, [Somme de test secteur] + 157,
[Somme de test secteur])
You might need to play with the column/measure references to fit your semantic model.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you Brightsider,
it's not perfect, because + 157 is for month 10. If I put months 1-10 for example, I will have to add 42 890$, not 157$.
Other proposition?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ah. Is the amount you need to add fixed per month (i.e. will month 10 always be 157, month 9 always be XYZ, etc)?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I've tried this:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
EDIT: Oops, I transposed the SUMX and the DIVIDE.
I think I'm seeing the shape of this a little better now. Try this:
IF(
Fusionner1[Secteurs division] = 1,
[Montant Measure] +
DIVIDE(
SUMX(
FILTER([Fusionner1[Clients.Secteur] = "20% MARINE, "20% HYDRO - 20% MACHINE - 40% PIPELINES"),
[Inserttheamountcolumnnameyouaresummingtogetthevaluehere]),
3),
[Montant Measure])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Same result, it doesn't add the 1/3 of sector "20%.. " in category 1,2,3
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ah, right. Try this:
TEST secteur 5 = IF(
Fusionner1[Division] = 1,
[Montant Measure] +
DIVIDE(
SUMX(
FILTER(REMOVEFILTERS(Fusionner1, [Clients.Secteur]), Fusionner1[Clients.Secteur ] = "20% MARINE, 20% HYDRO - 20% MACHINE - 40% PIPELINES"),
[Montant secteur]),
3),
[Montant Measure])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I try it now and let you know.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
No, we play with months and years too, so will be complicated to fix all possibilities adding this way.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ah. Is the amount that it adds just based on a percentage of the value for that Client.Secteur (meaning, we could derive the value we need to add by just multiplying the Somme by the percentage and then adding it in?) or are these values that you are receiving from a department to add to the value (meaning, they just tell you what number to add and you add it)?
The main question I have is how are you getting the number for Somme de test secteur for the 20% MARINE, 20% HYDRO... row at the bottom of your first image?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Somme de test secteur is just the same then the amount in my database. Sector 20%... concern only one client named BNAC
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
03-29-2024 08:36 PM | |||
09-04-2024 05:04 AM | |||
07-23-2024 06:36 AM | |||
08-06-2024 03:07 PM | |||
07-31-2024 11:01 AM |
User | Count |
---|---|
13 | |
13 | |
12 | |
8 | |
7 |
User | Count |
---|---|
18 | |
14 | |
11 | |
11 | |
9 |