Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Geo_Dor
Frequent Visitor

Sum of measure with selected value

hello everyone,

 

I have the following measures . All of them are corect except the last one the "Debit Balance 3". I want to sum this measure but the result at the total is not correct , although the individual results at the rows are ok.

 

Please help!!!!!!!!!!!

 

 

OBalance 3 =


var b =
SELECTEDVALUE('Date-Table'[Date])

var c =
CALCULATETABLE(
STARTOFYEAR(
'Date-Table'[Date]
),
'Date-Table'[Date]=b
)

return

CALCULATE(
sum(
'ERP Amount'[Amount (LCY)]
),
FILTER(
'ERP Amount',
'ERP Amount'[Posting Date]<
c

)
)

------------------------------------------------------------------------------

PCrebit 3 =


var b =
SELECTEDVALUE('Date-Table'[Date])

var c =
CALCULATETABLE(
STARTOFYEAR(
'Date-Table'[Date]
),
'Date-Table'[Date]=b
)


return

CALCULATE(
sum(
'ERP Amount'[Credit Amount (LCY)]
),
FILTER(
'ERP Amount',
'ERP Amount'[Posting Date]>=c
&&
'ERP Amount'[Posting Date]<=SELECTEDVALUE('Date-Table'[Date])-1

)
)

------------------------------------------------------------------------------

Dedit 3 =


var b =
SELECTEDVALUE('Date-Table'[Date])

var c =
SELECTEDVALUE('Date-Table2'[Date])

 

return

CALCULATE(
sum(
'ERP Amount'[Dedit Amount (LCY)]
),
FILTER(
'ERP Amount',
'ERP Amount'[Posting Date]>=b
&&
'ERP Amount'[Posting Date]<=c

)
)

------------------------------------------------------------------------------
Cumulative Dedit 3 =
[Dedit 3]+[PDebit 3]

 


------------------------------------------------------------------------------

Debit Balance 3 =


var a =
[OBalance 3]+[Cumulative Debit 3]-[Cumulative Credit 3]

var b=
IF(
a>0,
a,
0)


var c=
SUMX(
FILTER(
'ERP Amount',
a>0
),
a
)

var d=
IF(
HASONEFILTER(
'ERP Amount'[Customer No_]
),
b,
c
)

return
d

------------------------------------------------------------------------------

3 REPLIES 3
Sahir_Maharaj
Super User
Super User

Hello @Geo_Dor,

 

Try modifying the "Debit Balance 3" measure to use the ALL function to remove any existing filter context from the 'ERP Amount'[Customer No_] column, which may be causing the incorrect calculation at the total level:

Debit Balance 3 =

VAR a = [OBalance 3] + [Cumulative Debit 3] - [Cumulative Credit 3]

VAR b = IF(a > 0, a, 0)

VAR c = SUMX(FILTER('ERP Amount', a > 0), a)

VAR d = IF(HASONEFILTER('ERP Amount'[Customer No_]), b, c)

VAR e = CALCULATE(d, ALL('ERP Amount'[Customer No_]))

RETURN e

 

 


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

The addition of the CALCULATE function and ALL function with the 'ERP Amount'[Customer No_] column will remove any existing filter context on this column and ensure that the measure is calculated correctly at the total level.

 

Let me know if you might need further assistance.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Hello @Sahir_Maharaj ,
than you for your answer but unfortunately the total is completely wrong.
the measure calculates correcly the result at the rows of the table and it must sum this results at this column but it isnt happend

 

Thanks in andvace

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.