Issues on Line total in condition of measures

[Yanou]

Good evening to all,

I think this IS the good forum.

I use in the report view, in a table, a formula to do a condition (IF or Switch, even with calculate) with measures. The issue ils that the result Line details is correct but not the Line total. 

I can't send my data model bit I will write the formula that I used. To know :

-the formula result is correct in Line details

-each measure used in the formula works correctly in Line details and total line

Here is the formula (used Also with calculate without correct issue) :

RP finalisé = SWITCH(TRUE(),([Mt Financé après report et ajustement]+[RP à fin N-1])<[Funding],[Mt Financé après report et ajustement],([Mt Financé après report et ajustement]+[RP à fin N-1])=[Funding],[Mt Financé après report et ajustement],([Funding]-[RP à fin N-1]))

Thx for your help

Cdt.

PS : these measures work correctly :

Ajustements = CALCULATE(sum(‘Base conventions'[Ajustement (€)]))

Funding = CALCULATE(SUM(‘Base conventions'[Funding (€)]))

[tamerj1]

Hi @Yanou 
Please let me know which columns are you using in your Table/Matrix visual.

[Yanou]

Hi,

Tell me if you need other information.

Thx for your nice help

[Yanou]

Hello Tamerj1,

Thanks for your feed back. 

Here is a picture of ma model link view with all the fields and measures used. I also put an abstract of my table view to show you the context. I'm sorry I cant send the figures of the table but i can tell you that I nearly used only measures in the Table view.

The view : https://we.tl/t-HQV2B7PWBJ 

Now, I show you the measures that the result in the Table view is correct int the line details but wrong in the total line. Here is the 3 formulas (measures) :

1) Dépenses éligibles FAP/FHP V2 (apres T1/Date fin) = CALCULATE([Achat_IFA_PCS_Eligible_FAP FHP]+[Achats_Brutes_Elig._FAP FHP]+[MOE_Eligible_FHP]+[MOE_PCS_Eligible_FHP], FILTER('Base conventions', 'Base conventions'[Date de fin]>=[Last date]))

2) RP finalisé 1/2 = SWITCH(TRUE(),([Mt Financé après report et ajustement]+[RP à fin N-1])<=[Funding],[Mt Financé après report et ajustement],([Funding]-[RP à fin N-1]))

3) RP finalisé 2/2 = CALCULATE(IF([RP finalisé 1/2]=0,[Histo RP N à M-1],[RP finalisé 1/2]))

Thank you very much. If you are some advices for me to have the correct Total in the table^^.

Best regards

[tamerj1 · Accepted solution]

Hi @Yanou 
Please try

RP finalisé =
SUMX (
    SUMMARIZE (
        'SAP compta',
        'SAP compta'[Element OPT],
        'Base Conversions'[Libelle]
    ),
    VAR Ajdustment = [Mt Financé après report et ajustement]
    VAR FinN_1 = [RP à fin N-1]
    VAR Funding = [Funding]
    RETURN
        SWITCH (
            TRUE (),
            ( Ajdustment + FinN_1 ) < Funding, Ajdustment,
            ( Ajdustment + FinN_1 ) = Funding, Ajdustment,
            ( Funding - FinN_1 )
        )
)

[Yanou]

Hello my sauver,

Could you tell me what is wrong in my formula measure (always for the same visual view and data model) please ?

I would like a measure who test some date for each project and gives 0 if the date higher and an addition if the date is lower. This maesure below is good for the total but the test on  the date is wrong. And I don’t know why.

Dépenses éligibles FAP/FHP V2 (apres T1/Date fin) = SUMX (SUMMARIZE ('SAP compta','SAP compta'[Element OTP],'Base conventions'[Date de fin]

    ),

           IF('Base conventions'[Date de fin]>=[Last date],

           ([Achat_IFA_PCS_Eligible_FAP FHP]+[Achats_Brutes_Elig._FAP FHP]+[MOE_Eligible_FHP]+[MOE_PCS_Eligible_FHP]),0

        )

)

Information :

Date fin = LASTDATE('Base conventions'[Date de fin])   >>>> limit date of the contract

Last date = MAX('SAP compta'[Date de document])        >>>> last Date of expenses in the data base

Thx Tamerj

[tamerj1]

Hi @Yanou 

sorry I did not understand your requirement. Please clarify further. 

[Yanou]

Hi. I would like to thanks you again, with the Summarize you give me a formula that I can nearly adapt in all situation ! For the Big goal : a measure with a right total Line !!!

[tamerj1]

@Yanou 

Sorry for the inconvenience. Please let me if we can connect via teams or zoom to look into it.

[Yanou]

Thanks a lot for your concern. But you already help me and in fact the total was good. The issue was when my measure's figure was rounded to 2 digits after decimal dot, it made a cents variance versus total amounted by Excel (exported visual table). I have to use 4 digits After decimal dit to manage that.

And my oral english is too Bad for a Teams Session 🤣😅.

Best regards

[Yanou]

I'm sorry. I was wrong. Instead of, do you know how to tell PBI to spot the latest date in a column for account ? The accounts are in a column of a table, and there are for each account an opération date.

• Do you know how to spot for each line the latest date and enter it for all the identical account ? On the column accounts, thé same account can be entered several times. Ans I would like to enter the New date in a New calculated column.

Thanks you. I learn a lot thanks you all

[Yanou]

Thanks you so much

[Yanou]

Hello Tamerj1,

I would like to thank you for the super tip ! After adapting your formula in my data model, it works ! I don't exactly understand the formula but thank you.

Can you help me a last time with these formula ?

Dépenses éligibles FAP/FHP V2 (apres T1/Date fin) = CALCULATE([Achat_IFA_PCS_Eligible_FAP FHP]+[Achats_Brutes_Elig._FAP FHP]+[MOE_Eligible_FHP]+[MOE_PCS_Eligible_FHP], FILTER('Base conventions', 'Base conventions'[Date de fin]>=[Last date]))

I know I have to remove the "Calculate" and add a SUMX and a SUMMURAZE so, but I don't have to manage to do it because of the "Filter". How to do that ?

Have a nice evening or night.

[Yanou]

Thanks you for help. I was in football training. I gonna try your tip now.

[Yanou]

Sorry, my english is so bad ...

I would like you to correct my measure. The measure works in my visual table for the detail Line, but the total Line isn't good.

Do you think my formula is good ?

Thanks