how to make a conditional sum in a filtered summarized table

[Anonymous]

Hello,

I have made a summarized table using the following DAX:

Summary Table =

FILTER(

SUMMARIZE('TransXML Children Level','TransXML Children Level'[SBatchName],'TransXML Children Level'[FileName],'TransXML Children Level'[LockBox],'TransXML Children Level'[ResultStatus],'TransXML Children Level'[Batch Status],'TransXML Children Level'[Transaction Date].[Date],'TransXML Children Level'[Period],'TransXML Children Level'[Account],'TransXML Children Level'[NbInvoiceBAI],"number of invoices Posted",DISTINCTCOUNTNOBLANK('TransXML Children Level'[CorInvoice]),"TotalAmount",sum('TransXML Children Level'[Amount])),'TransXML Children Level'[ResultStatus]="s" && 'TransXML Children Level'[Batch Status]="A" && 'TransXML Children Level'[Account]="100" )

Now my needs has evoluated and I would like to get the number of none blank record when 'TransXML Children Level'[Batch Status]="A", the number of posted invoices and 'TransXML Children Level'[Batch Status]="N" , the number of none posted invoice While keeping the other condition on the summarize table such as :  

'TransXML Children Level'[ResultStatus]="s" && 'TransXML Children Level'[Account]="100"

So I though that my script could look like below

Summary Table =

FILTER(

SUMMARIZE('TransXML Children Level','TransXML Children Level'[SBatchName],'TransXML Children Level'[FileName],'TransXML Children Level'[LockBox],'TransXML Children Level'[ResultStatus],'TransXML Children Level'[Batch Status],'TransXML Children Level'[Transaction Date].[Date],'TransXML Children Level'[Period],'TransXML Children Level'[Account],'TransXML Children Level'[NbInvoiceBAI],"number of invoices Posted",DISTINCTCOUNTNOBLANK('TransXML Children Level'[CorInvoice]) with batch Status = "A" ,"number of invoices  none Posted",DISTINCTCOUNTNOBLANK('TransXML Children Level'[CorInvoice]) with batch Status = "N","TotalAmount",sum('TransXML Children Level'[Amount])),'TransXML Children Level'[ResultStatus]="s" && 'TransXML Children Level'[Account]="100" )

How can we make a conditional sum (Batch Status ="A" or Batch Status="N") while filtering the rest of the summarized table using those two coditions: 'TransXML Children Level'[ResultStatus]="s" && 'TransXML Children Level'[Account]="100"

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Please try to create a calculated table as below to see if it works.

Summary Table =
FILTER (
    SUMMARIZE (
        'TransXML Children Level',
        'TransXML Children Level'[SBatchName],
        'TransXML Children Level'[FileName],
        'TransXML Children Level'[LockBox],
        'TransXML Children Level'[ResultStatus],
        'TransXML Children Level'[Batch Status],
        'TransXML Children Level'[Transaction Date].[Date],
        'TransXML Children Level'[Period],
        'TransXML Children Level'[Account],
        'TransXML Children Level'[NbInvoiceBAI],
        "number of invoices Posted", CALCULATE (
            DISTINCTCOUNT ( 'TransXML Children Level'[CorInvoice] ),
            FILTER (
                'TransXML Children Level',
                'TransXML Children Level'[CorInvoice] <> BLANK ()
                    && 'TransXML Children Level'[Batch Status] = "A"
            )
        ),
        "number of invoices  none Posted", CALCULATE (
            DISTINCTCOUNT ( 'TransXML Children Level'[CorInvoice] ),
            FILTER (
                'TransXML Children Level',
                'TransXML Children Level'[CorInvoice] <> BLANK ()
                    && 'TransXML Children Level'[Batch Status] = "N"
            )
        ),
        "TotalAmount", SUM ( 'TransXML Children Level'[Amount] )
    ),
    'TransXML Children Level'[ResultStatus] = "s"
        && 'TransXML Children Level'[Account] = "100"
)

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.