Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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: inputpic 1: input

 ABC
1KPIValuePeriod
2Revenue  March 2020
3Cost March 2020
1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@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_)

 

 Result value.JPG

 

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

View solution in original post

6 REPLIES 6
V-pazhen-msft
Community Support
Community Support

@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_)

 

 Result value.JPG

 

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

Anonymous
Not applicable

Hi @V-pazhen-msft,

 

That works perfect. Thanks a lot! 

 

@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.

dethompson97
Resolver III
Resolver III

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)

 

 

DAX Question.jpg

 

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

Anonymous
Not applicable

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.