topic Re: How to calculate Filterd VAR table by cumulative total in DAX Commands and Tips

How to calculate Filterd VAR table by cumulative total

ohnothimagain — Sun, 11 Aug 2024 11:04:20 GMT

Hello everyone. I came back again...
I want to make a table filtered and summary values cumulative,but this doesn't work.How should I correct it?

Ftd_sum := VAR tbl = SELECTCOLUMNS( FILTER(ALL('TEST_'), 'TEST_'[STS] = "〇"), "@Trg", 'TEST_'[PN] ) VAR Dis = DISTINCT(tbl) VAR Ftd = CALCULATETABLE( 'TEST_', 'TEST_'[PN] IN Dis, FILTER( ALLSELECTED('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ), USERELATIONSHIP('Calendar'[Date], 'TEST_'[Calculated]) ) VAR Result = CALCULATE( SUM('TEST_'[Qty]), KEEPFILTERS(Ftd) ) RETURN Result

Table (I'm doing it on powerpivot)

want it to be

Thank you for reading,and continuous support!
(Thank AnonymousNinja for cooperation previously,but still unsuccess)

Best Regards,

Re: How to calculate Filterd VAR table by cumulative total

lbendlin — Sun, 11 Aug 2024 22:50:58 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: How to calculate Filterd VAR table by cumulative total

ahadkarimi — Mon, 12 Aug 2024 00:12:30 GMT

Heyohnothimagain,
Try this code, and if you encounter any issues, let me know:

Ftd_sum :=
VAR tbl =
SELECTCOLUMNS(FILTER(ALL('TEST_'), 'TEST_'[STS] = "〇"),
"@Trg", 'TEST_'[PN])
VAR Dis =
DISTINCT(tbl)
VAR Ftd =CALCULATETABLE(
'TEST_',FILTER(ALLSELECTED('TEST_'),'TEST_'[PN] IN Dis))
VAR Result =CALCULATE(SUM('TEST_'[Qty]),

FILTER(ALLSELECTED('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date])),KEEPFILTERS(Ftd))
RETURN
Result

Re: How to calculate Filterd VAR table by cumulative total

ohnothimagain — Mon, 12 Aug 2024 01:46:01 GMT

Thank you for your reply! but I'm afraid to say that I'm not in usable condition so far.

Anyway,once again I really appreciate your support.

Re: How to calculate Filterd VAR table by cumulative total

ohnothimagain — Mon, 12 Aug 2024 03:31:44 GMT

Thank you for your reply! I tried right away,definately got closer! (got cumulative total)
I have additional questions;

1. I need to add "use relationship" how should I write this..?

following doesn't work.

var Ftd =CALCULATETABLE('TEST_', filter(allselected(TEST_),'TEST_'[PN] IN Dis), USERELATIONSHIP('Calendar'[Date],'TEST_'[Calculated]))

2.If I need to calculate as the same way with distinct [PO]column,how should I do it??

Best regards,

Re: How to calculate Filterd VAR table by cumulative total

ahadkarimi — Mon, 12 Aug 2024 13:08:50 GMT

Hi ohnothimagain, go ahead and use this code, and if there are any issues, just let me know.

Ftd := CALCULATETABLE( 'TEST_', FILTER(ALLSELECTED('TEST_'), 'TEST_'[PN] IN Dis), USERELATIONSHIP('Calendar'[Date], 'TEST_'[Calculated]) )

Then, use :

Ftd_sum := VAR tbl = SELECTCOLUMNS( FILTER(ALL('TEST_'), 'TEST_'[STS] = "〇"), "@Trg", 'TEST_'[PN] ) VAR Dis = DISTINCT(tbl) VAR Ftd = CALCULATETABLE( 'TEST_', FILTER(ALLSELECTED('TEST_'), 'TEST_'[PN] IN Dis), USERELATIONSHIP('Calendar'[Date], 'TEST_'[Calculated]) ) VAR Result = CALCULATE( SUM('TEST_'[Qty]), FILTER( ALLSELECTED('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ), KEEPFILTERS(Ftd) ) RETURN Result

Calculate distinct [PO]column:

Ftd_sum_PO := VAR tbl = SELECTCOLUMNS( FILTER(ALL('TEST_'), 'TEST_'[STS] = "〇"), "@Trg", 'TEST_'[PO] -- Change to the PO column ) VAR Dis = DISTINCT(tbl) VAR Ftd = CALCULATETABLE( 'TEST_', FILTER(ALLSELECTED('TEST_'), 'TEST_'[PO] IN Dis), USERELATIONSHIP('Calendar'[Date], 'TEST_'[Calculated]) ) VAR Result = CALCULATE( SUM('TEST_'[Qty]), FILTER( ALLSELECTED('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ), KEEPFILTERS(Ftd) ) RETURN Result

Re: How to calculate Filterd VAR table by cumulative total

ohnothimagain — Tue, 13 Aug 2024 05:06:50 GMT

Thank you again!
I just copied that you wrote as "Ftd_sum2" ,but unfortunately it was unsuccess..(blank values)
can you think of anything that might have caused it??

Best regards,