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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nikos_derv
Frequent Visitor

aggregate per category keeping filters, using calculated column or new table

Hello,

 

I have a customer-per-month dataset, and i need to create a calulated column (not measure) where for each customer returns an aggregation of its total turnover. The tricky part here is that i need to have a slicer in my dashboard, where the user selects specific months to be included in this calculation.

 

Please note that it need to be a calculation in column, not a measure, because i need to use this numbers as axis in a scatter chart.

 

I tryed a calculated new table having both ways connection with my primary customer-per-month table, on customer_id . It did returned on customer level the required aggregation, but it partially ignored the slicer, as it was containing total turnover, of the correct month's customers.

 

My code was the following:

 

TOTAL_CUSTOMER_TURNOVER =
SUMMARIZE(
'CUSTOMERS_MONTHLY_ACT'
,CUSTOMERS_MONTHLY_ACT[CUSTOMER_ID]
,"Turnover",sum(CUSTOMERS_MONTHLY_ACT[MONTHLY_TURNOVER])
 
I seems that if i used some kind of filtering in my calculated table it will be wroking fine, but i just can't figure it out.. Can someone help me please?
 
Many thanks in advance!
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@nikos_derv , Only measure can use slicer value, not calculated table/column.

 

You can use dynamic segmentation to get measure on Axis.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@nikos_derv , Only measure can use slicer value, not calculated table/column.

 

You can use dynamic segmentation to get measure on Axis.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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