Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
robbiecutting
Frequent Visitor

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

1 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.Smiley Happy

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

 

Regards

View solution in original post

3 REPLIES 3
robbiecutting
Frequent Visitor

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

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

 

 

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.Smiley Happy

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

 

Regards

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.