Create a filtered and grouped calculated table

[robbiecutting]

Hi, I'm relatively new to power bi so please bear with me!

If I have a table which lists the invoices for all customers, across all stores, can I create a calculated table which filters out a certain customer and groups all invoices by store level?

For example if I had a slicer which lists all customers, I would like the user to select a single customer to view. I would then like to create a new calculated table which filters out only the invoices for the customer selected in the slicer. I would then like to group all invoices by each store of the given customer, to see the total invoice revenue for each store. Is this possible?

Also, I would need the calcualted table to be recreated everytime the slicer changes? Is this something I can achieve in power bi?

Thanks in advance

[robbiecutting]

I have found the SUMMARIZE function will achieve what I need regarding the grouping of stores. I can also use CALCULATETABLE to filter out certain criteria (Only bring invoices from 2017 etc). Is there a way I can use the filter in CALCULATETABLE to filter the slicer selection? Is there a way I can reference the selected slicer value?

Thanks

[robbiecutting]

I have got the calculated table in the correct format using the below query:

Customer Sales = FILTER(FILTER(CALCULATETABLE(SUMMARIZE(Invoices,Invoices[CustomerDWID], Invoices[FinMonthNum], Customers[InvoiceName], "Total Sales", SUM(Invoices[Invoice_Amount])),'Date'[YearValue] = 2017), Invoices[FinMonthNum]=CurrentDate[CurrentMonth]-1), Customers[InvoiceName] = "ACCOUNT TO VIEW")

This is achieved by manually entering the invoice account (ACCOUNT TO VIEW), is there a way I can dynamically set this value based on the current selection of a given slicer?

Thanks

[v-ljerr-msft · Accepted solution]

Hi @robbiecutting,

Not like measures, calculate tables/columns are evaluated during the database processing(like a data refresh) and then stored in the model, and are not affected by user selection on the report. 

In this scenario, I would suggest you to create a measure to calculate the "Total Sales" with corresponding conditions, then show the measure on a Table visual with other columns. The formula below is for your reference.

Total Sales =
CALCULATE (
    SUM ( Invoices[Invoice_Amount] ),
    FILTER (
        Invoices,
        Invoices[FinMonthNum]
            = MAX ( CurrentDate[CurrentMonth] ) - 1
    )
)

Regards