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 cer...
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

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