March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 (€)]))
Solved! Go to 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 )
)
)
Hi @Yanou
Please let me know which columns are you using in your Table/Matrix visual.
Hi,
Tell me if you need other information.
Thx for your nice help
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) :
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 )
)
)
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
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 !!!
Sorry for the inconvenience. Please let me if we can connect via teams or zoom to look into it.
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
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.
Thanks you. I learn a lot thanks you all
Thanks you so much
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 ?
Thanks you for help. I was in football training. I gonna try your tip now.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
27 | |
27 | |
21 | |
19 |