Forum Discussion
Helper ICreate a measure or calculated column based on 2 columns
Sample data:
I want to create two measures/calculated columns.
One showing the 'Total Amount(Sum)' for each Date & Name combination for category c1.
Another showing the 'Count' for each Date & Name combination for category c1.
I also want this measure to adhere to Date slicer. I am using date dimension table for the date slicer.
Appreciate any help!
Hi,
Ensure that the Date column has actual Date entries. Create a relationship from the Date column of your sample dataset to the Date column of your Calendar Table. In the slicer, drag Date from the Calendar Table. In your Table visual, drag Date, Name and Category from the sample dataset. Write these measures
Total=SUM(Data[Amount])
Count=COUNTROWS(Data)
Hope this helps.
2 Replies
v-yulgu-msftMicrosoft Employee
Hi SR1,
Measures.
Total Amount = CALCULATE ( SUM ( Table_2[Amount] ), FILTER ( ALLEXCEPT ( Table_2, Table_2[Date], Table_2[Name] ), Table_2[Category] = "c1" ) ) Total count = CALCULATE ( COUNT ( Table_2[Amount] ), FILTER ( ALLEXCEPT ( Table_2, Table_2[Date], Table_2[Name] ), Table_2[Category] = "c1" ) )Best regards,
Yuliana Gu
- Ashish_Mathur
Super User
Hi,
Ensure that the Date column has actual Date entries. Create a relationship from the Date column of your sample dataset to the Date column of your Calendar Table. In the slicer, drag Date from the Calendar Table. In your Table visual, drag Date, Name and Category from the sample dataset. Write these measures
Total=SUM(Data[Amount])
Count=COUNTROWS(Data)
Hope this helps.