Grouping by dates & summing the charge

[ToddMate]

Hi,

I'm in need of assistance, i have a table containing service numbers, charge from (Date) and charge to (Date), an invoice period and an amount.

What i need to achieve is to determine the SUM of the amount where the charge from (date) and charge to (date) are the same, and then group these by service number and invoice period within a Matric (Service Number is the Row and Invoice Period is the Column)

For the life of me i am unable to achieve the desired result, can anyone help here?

I have the sample PBI file in need, however i am unsure how to attached it to this post.

[v-eachen-msft]

Hi @ToddMate ,

You can create a new measure like this:

Measure =
CALCULATE (
    SUM ( ImportFile[Charge] ),
    ALLEXCEPT (
        ImportFile,
        ImportFile[Charge From],
        ImportFile[Charge To],
        ImportFile[ServiceNumber],
        ImportFile[Invoice Period]
    )
)

[ToddMate]

Hi,

Having reread my post i have been vague and inaccurate in my requirements in that what i need to achieve. My apologies for this.

What i actually need to achieve is to sum the charge column were the Charge From and Charge To dates are the same as other rows. The context will be applied in the matrix by Service Number and Invoice Period.

The matrix would have Service Number as rows and Invoice Period as columns the values would be the sum of the charge. In the highlighted example below this would return $2.80 for the 01/05/2017 Invoice Period. (Service Number AX1542888)

If there are no matching rows then nothing would be shown. 

This is a link to the above data in a sample file on my onedrive (password = power). 
https://1drv.ms/u/s!AqPKTVzB9hvp9l_Yw-WOCeNJiq6W?e=CllYPN

Regards
Todd

[sturlaws · Accepted solution]

Now that is a bit more tricky question. Thanks for the sample-file, for the purpose of testing it would have been nice if there some cases where there were no matching to/from-rows

A couple of different ways to solve this. You could create a calculated column, where 1 indicates matching rows and 0 rows without match. Then add amount/charge to the matrix visual, and filter on flag=1

matchingFlag =
VAR sn = ImportFile[ServiceNumber]
VAR cf = ImportFile[Charge From]
VAR ct = ImportFile[Charge To]
VAR ip = ImportFile[Invoice Period]
RETURN
    IF (
        COUNTROWS (
            FILTER (
                ImportFile;
                ImportFile[ServiceNumber] = sn
                    && ImportFile[Charge From] = cf
                    && ImportFile[Charge To] = ct
                    && ImportFile[Invoice Period] = ip
            )
        ) > 1;
        1;
        0
    )

Or you can create this measure

amount =
CALCULATE (
    SUM ( ImportFile[Charge] );
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                ImportFile;
                ImportFile[Invoice Period];
                ImportFile[ServiceNumber];
                ImportFile[Charge From];
                ImportFile[Charge To]
            );
            "numberOfRows"; CALCULATE ( COUNTROWS ( ImportFile ) )
        );
        [numberOfRows] > 1
    )
)

Cheers,
S

[amaleranda]

hi @ToddMate 

Please accept this as a solution if it resolver your issue, 

[amaleranda]

Hi @ToddMate 

have you tried make charge column default summrisation to be sum and then put them in a tabel. 

that is what I did in the below tabel. there are only two line items in the details tabel that have same from and to date. 

[sturlaws]

Hi,

you can share the file by uploading it to onedrive/dropbox/other and post the link to the file.

Cheers,

S

[sturlaws]

But, assuming I have understood you requirements, your measure could be written like this

Amount =
CALCULATE (
    SUM ( Table[charge] );
    FILTER ( Table; Table[charge from] = Table[charge to] )
)

and add this measure together with invoice period and servicenumber to your matrix visual