Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Different calculation for the same column

Hi,   I have a table as shown in the pic 1. "Cost" and "Revenue" are my Key Performance Indicators (KPI). I want to create a table visual to show monthly KPIs. Please check the table for desired ou...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous 

    Try the two measures and put into a table visual with [KPI]. If they are not working, you need to change the data type of [Period] column from Text ("January 2020") to Date like "2020/1/1", so you can use DAX to find the latest date.

     

    Latest Period = LASTDATE('Table'[Period])

Result Value = 
var Cost_ = CALCULATE(AVERAGE('Table'[Value]),DATESYTD('Table'[Period]),FILTER('Table','Table'[KPI]="Cost"))
var Revenue_ = CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[Period]=MAX('Table'[Period])),'Table'[KPI]="Revenue")

Return SWITCH(MAX('Table'[KPI]),
"Revenue",Revenue_,"Cost", Cost_)

     

     

     

    Paul Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

dethompson97's avatar
dethompson97
Resolver III
6 years ago

Hi! I am new to this but thought I would challenge myself and give it a try.  Here is my solution.  In order to do this, you need to write a measure that will change the filter context so that you are only looking at the last month (if I understood the question clearly)

 

 

 

Hope this helps!

Dawn Thompson

 

PS:  If this solution works, please mark it as a solution to help others. Kudos are nice too.

dethompson97's avatar
dethompson97
Resolver III
6 years ago

Sorry for the uncaught spelling error.  It should say LastPeriod not LastPersion

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi dethompson97,

     

    Thanks for your reply. I need YTD  average for Cost and latest month for Revenue. Please check the reply by V-pazhen-msft.