How do I create a summary table where I can use a slicer without displaying or summarizing by it..

[Rocky_Brown]

I have a data source that gives me shipDate with a total Invoice amount.  The same table displays an Actual Amount of Invoice .  I want a summary table that gives me the total of the Invoice and the total per ship date of the Actual amount bering able to filter on Ship date, but not displaying in the Summary.

Example:

Data source

Ship Date                Invoice #                         Invoice Date               Invoiced Amount                        Actual Amount

03/06/2020                1                                    03/19/2020                     1000.00                                   250.00

03/06/2020                 1                                   03/19/2020                     1000.00                                   300.00

03/06/2020                  1                                  03/19/2020                     1000.00                                   275.00

I want to display the summary data by Invoice as follows:

Amt Invoiced:  1000                   Actual Amount:  825.00

Using Ship Date to be ablew to be used a s a slicer.

Any help would be appreciated.

[MFelix · Accepted solution]

Hi @Rocky_Brown ,

If the value of the Invoice value is always the same for all line what you can do is to create a measure for the values invoiced, since you can have more than one invoice per day you will need to use the SUMX function, and for the actual amount just a sum.

Do the following measures:

Invoice Total = SUMX(VALUES('Table'[Invoice #]), MIN('Table'[Invoiced Amount]))

Actual Total = SUM('Table'[Actual Amount])

Has you can see below the total get calculated correctly:

[Rocky_Brown]

Thank you for the info.  This is great!

[MFelix]

Don't forget to mark the correct answer to help others. 

[Rocky_Brown]

Kind of.  I am trying to put the data on a matrix and am not displaying Invoce#.  I am dispalying the data by Warehouse, then the 2 totals.  the calcs work when I put on a table.  The total for the calculated field displays as zero, and the amount invoiced repeats the total:

Warehouse:           Invoiced Amount            Actual AMount

a                             $1400 (total of invoice)                    4,000 (Grand Total)

b                              $300 (Total OF Invoice)                   4,000 (Grand Total

TOTALS:                         $0                                              $4000,00

Dont know if this can be fixed.

Thank you

[MFelix]

Hi @Rocky_Brown,

This type of measures are depending on context so if instead of invoice you are calculating on wharehouse the calculation needs to be different. Can you share a more complete sample? 

[Rocky_Brown]

My matrix shows the following:

Warehouse                Total Invoiced (total of entire invoice)            Actiual Amount (Per Invoice)

XXX                              1,000                                                               875  

Data source consists of:

Ship Date                Invoice #                         Invoice Date               Invoiced Amount                        Actual Amount

03/06/2020                1                                    03/19/2020                     1000.00                                   250.00

03/06/2020                 1                                   03/19/2020                     1000.00                                   300.00

03/06/2020                  1                                  03/19/2020                     1000.00                                   275.00

Does this help?