Forum Discussion

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

Create 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!

Need Help
Tutorial Requests
  • v-danhe-msft's avatar
    v-danhe-msft
    8 years ago

    Hi SR1,

    Based on my test, you could refer to below steps:

    Sample data:

    Create two measures:

    Sum = CALCULATE(SUM('Table1'[Amount]),FILTER('Table1','Table1'[Date]=MAX('Table1'[Date])&&'Table1'[Category]="c1"))
    Count = CALCULATE(COUNT('Table1'[Amount]),FILTER('Table1','Table1'[Date]=MAX('Table1'[Date])&&'Table1'[Category]="c1"))

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

2 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi SR1,

    Based on my test, you could refer to below steps:

    Sample data:

    Create two measures:

    Sum = CALCULATE(SUM('Table1'[Amount]),FILTER('Table1','Table1'[Date]=MAX('Table1'[Date])&&'Table1'[Category]="c1"))
    Count = CALCULATE(COUNT('Table1'[Amount]),FILTER('Table1','Table1'[Date]=MAX('Table1'[Date])&&'Table1'[Category]="c1"))

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi SR1,

    Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

     

    Regards,

    Daniel He