Easy to fix! DAX formula

[sportive92003]

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!

[v-jialongy-msft]

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.

[Brightsider]

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.

[sportive92003]

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?

[Brightsider]

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)? 

[sportive92003]

I've tried this: 

test secteur =

VAR A = CALCULATE([Montant Measure], Fusionner1[Clients.Secteur ] = "01 -Machinerie et travaux")

VAR B = CALCULATE([Montant Measure], Fusionner1[Clients.Secteur ] = "02 -Marine et portuaire")

VAR C = CALCULATE([Montant Measure], Fusionner1[Clients.Secteur ] = "03 -Hydroélectrique")

VAR Montant_BNAC = IF(Fusionner1[Secteurs division] = 2, Fusionner1[Montant secteur /3 Measure])

VAR A_BNAC = A + Montant_BNAC

VAR B_BNAC = B + Montant_BNAC

VAR C_BNAC = C + Montant_BNAC

RETURN

    SWITCH(

        TRUE,

        Fusionner1[Clients.Secteur ] = "01 -Machinerie et travaux", A_BNAC,

        Fusionner1[Clients.Secteur ] = "02 -Marine et portuaire", B_BNAC,

        Fusionner1[Clients.Secteur ] = "03 -Hydroélectrique", C_BNAC,

        Fusionner1[Clients.Secteur ] = " 20% MARINE, 20% HYDRO - 20% MACHINE - 40% PIPELINES", Montant_BNAC,

        Fusionner1[montant])

_____________________

Same results then my normal amount in each sector

I've tried this too: 

Test secteur 4 = IF([Division] = 1, [Montant Measure] + [BNAC],

[Montant Measure]).

ame results then my normal amount in each sector. Igual to test secteur

[Brightsider]

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])

[sportive92003]

Same result, it doesn't add the 1/3 of sector "20%.. " in category 1,2,3

TEST secteur 5 = IF(

Fusionner1[Division] = 1,

    [Montant Measure] +

    DIVIDE(

       SUMX(

            FILTER(Fusionner1, Fusionner1[Clients.Secteur ] = "20% MARINE, 20% HYDRO - 20% MACHINE - 40% PIPELINES"),

            [Montant secteur]),

      3),

     [Montant Measure])

[Brightsider · Accepted solution]

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])

[sportive92003]

I try it now and let you know.

[sportive92003]

No, we play with months and years too, so will be complicated to fix all possibilities adding this way.

[Brightsider]

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? 

[sportive92003]

Somme de test secteur is just the same then the amount in my database. Sector 20%... concern only one client named BNAC