Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have Formula column that each value is referred to the Id column. I need to caculate total amount based on the formula column.
For ex:
ReportLayout.Formula(D215) = Line ID(215)
The below formula works fine for the sequence numbers
Could you please advise how we can acheive this
Thanks
Vinoth S
Solved! Go to Solution.
hi, @Vinothsusai
Just adjust the formula as below:
Measure 4 = VAR maxf = MAX ( HANGeneralJournals_Remodify[Column 3]) RETURN IF ( maxf = BLANK (),0 , CALCULATE(SUM(HANGeneralJournals_Remodify[Custom Main Amount]),FILTER(ALL(HANGeneralJournals_Remodify),ISBLANK([ReportLayout.Line ID])=FALSE()&&CONTAINSSTRINGEXACT(maxf,"D"&[ReportLayout.Line ID]))))
Result:
For example, in your sample pbix file, the formula is "D231+D276+D284+D281+D282+D283", So I use a custom column 3 in the example:
Best Regards,
Lin
HI, @Vinothsusai
You could use this simple formula:
Measure = VAR maxf = MAX ( HANGeneralJournals_Remodify[ReportLayout.Formula] ) RETURN IF ( maxf = BLANK (),0 , CALCULATE(SUM(HANGeneralJournals_Remodify[AccountingCurrencyAmount]),FILTER(ALL(HANGeneralJournals_Remodify),CONTAINSSTRINGEXACT(maxf,[ReportLayout.Line ID]))) )
here is simple sample example:
Best Regards,
Lin
Hi,
Thank you very much for your reply.
The calculated measure values are not correct. Could you please advise where i did mistake in the report.
Please see the below screen shot.
Thanks
Vinoth SUSAINATHAN
hi, @Vinothsusai
Do you have done any filter by slicer, if so try ALLSELECTED instead of ALL in this formula.
And if you still have the problem, please share a sample pbix file.
Best Regards,
lin
Hi,
Thank you for your reply.
I have tried with ALLSELECTED. It seems something wrong in calculated result. Could you please advise.
Thanks
Vinoth SUSAINATHAN
hi, @Vinothsusai
Please share the sample pbix file.
You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
Hi
Please find the link
Thanks
Vinoth S
hi, @Vinothsusai
Just adjust the formula as below:
Measure 4 = VAR maxf = MAX ( HANGeneralJournals_Remodify[Column 3]) RETURN IF ( maxf = BLANK (),0 , CALCULATE(SUM(HANGeneralJournals_Remodify[Custom Main Amount]),FILTER(ALL(HANGeneralJournals_Remodify),ISBLANK([ReportLayout.Line ID])=FALSE()&&CONTAINSSTRINGEXACT(maxf,"D"&[ReportLayout.Line ID]))))
Result:
For example, in your sample pbix file, the formula is "D231+D276+D284+D281+D282+D283", So I use a custom column 3 in the example:
Best Regards,
Lin
Hi,
Thank you very much for your answer. It works fine.
Thanks
Vinoth S
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |