Create a measure or calculated column based on 2 columns

[SR1]

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!

[Ashish_Mathur · Accepted solution]

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.

[v-yulgu-msft]

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