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.

Anonymous's avatar
Anonymous
Not applicable
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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous,

 

That works perfect. Thanks a lot! 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous 

     

    You can use FORMAT() to set it partly, e.g. FORMAT(delivery_precision,"Percent")).

    Something like this:

     

     

    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")
var delivery_precision= CALCULATE(AVERAGE('Table'[Value]),DATESYTD('Table'[Period]),FILTER('Table','Table'[KPI]="delivery precision"))

Return SWITCH(MAX('Table'[KPI]),
"Revenue",Revenue_,"Cost", Cost_,"delivery precision",FORMAT(delivery_precision,"Percent"))

     

     

    You can also find other formats here if needed: https://docs.microsoft.com/en-us/dax/pre-defined-numeric-formats-for-the-format-function

     

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