Forum Discussion

SR1's avatar
SR1
Icon for Helper I rankHelper I
8 years ago
Solved

Create 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...
Need Help
Show and Tell
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    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's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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