Forum Discussion

robbiecutting's avatar
robbiecutting
Frequent Visitor
9 years ago
Solved

Create a filtered and grouped calculated table

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

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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.:smileyhappy:

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

     

    Regards

3 Replies

  • robbiecutting's avatar
    robbiecutting
    Frequent Visitor
    9 years ago

    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's avatar
      robbiecutting
      Frequent Visitor
      9 years ago

      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's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        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.:smileyhappy:

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

         

        Regards