cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper II

## Issues on Line total in condition of measures

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

Cdt.

PS : these measures work correctly :

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

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

1 ACCEPTED SOLUTION
Super User

Hi @Yanou

``````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 )
)
)``````
14 REPLIES 14
Super User

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

Helper II

Hi,

Tell me if you need other information.

Helper II

Hello Tamerj1,

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

Super User

Hi @Yanou

``````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 )
)
)``````
Helper II

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

Super User

Hi @Yanou

Helper II

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 !!!

Super User

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

Helper II

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

Helper II

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

Helper II

Thanks you so much

Helper II

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.
Helper II

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

Helper II

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