Forum Discussion
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 output. In the value column I want to show latest month value for "Revenue" (cell: B2) which in this case will be 170 as Revenue for March 2020 is 170. However, in the value column for "Cost" (cell: B3) , I want to show average YTD cost. For March 2020 it will be (100+120+130)/3 = 116.67. As a new month comes, the C column should update to April 2020, May 2020 and so on. Can you please help me with these?
(Row numbers and column letters are not needed in the visual. Just added to ease the explanation of the problem)
Thanks in advance!
pic 1: input
| A | B | C | |
| 1 | KPI | Value | Period |
| 2 | Revenue | March 2020 | |
| 3 | Cost | March 2020 |
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.
6 Replies
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
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.
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.
Sorry for the uncaught spelling error. It should say LastPeriod not LastPersion
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.
