cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lbad1601 Regular Visitor
Regular Visitor

TOTALYTD not working on calculated table

Hi All,

 

Tried everyting but no luck. Bit urgent. I created new table from existing table using calculatetable and summarize function. The requirement is to calculate Loss Factor which is nothing but sum of loss / sum of profit. The value for sum of loss and sum of profit are derived from single column, project profit. If project profit is positive then it will be profit, if it's in negative then it will be loss. This is how they are calculating currently in excel. But when i tried doing it, project profit itself is measure and i was not able to sum the values and compare in single measure. So i decided to create new table with all these values as columns. CALCULATETABLE(SUMMARIZE(Data,Data[Date],Data[Year],Data[C1],Data[C2],Data[C3]
,"Gross Revenue",CALCULATE(SUM(Data[C4])*-1,FILTER(Overhead,Overhead[Type]="Gross Revenue"))
,"Direct Expenses",Similar expression as above filtered for respective type
,"Staff Costs",CALCULATE(SUM(Data[Activity Amount]),FILTER(Overhead,Overhead[Overhead Type]="Staff Costs"))
,"OHonSubConsultant",CALCULATE(SUM(Data[Activity Amount]),FILTER(Overhead,Overhead[Overhead Type]="Overheads on sub-cons/procurement"))
),FILTER(Data,Data[FE/OH Flag] = "FE")). In this table i created new column project profit as gross revenue - ( Direct Expenses + Staff Costs + OHonSubConsultant). When checked with business he said that instead of using monthly numbers for project profit they are using YTD numbers and then calculating profit and loss, as shared above. I created new calculated column using TOTALYTD(SUM(Project Profit),Calendar'Date') but instead of giving YTD numbers it was giving me monthly numbers only. Relationship is there between Calendar table and newly created calculated table. Please let me know if there is somehing i'm missing or doing wrong. I want Project Profit YTD value in column so that i calculate profit and loss or directly loss factor using measure.

2 REPLIES 2
Community Support Team
Community Support Team

Re: TOTALYTD not working on calculated table

Hi @lbad1601 

Project Profit is a column in your dataset, right?

cate date Project Profit
a 1/1/2019 3
a 2/1/2019 2
a 3/1/2019 6
a 4/1/2019 4
b 1/1/2019 2
b 2/1/2019 3
b 3/1/2019 4
b 4/1/2019 5

You want to calculate YTD for Project Profit, then calcualte sum of loss and sum of profit based on Project Profit YTD, right?

 

Best Regards
Maggie

lbad1601 Regular Visitor
Regular Visitor

Re: TOTALYTD not working on calculated table

Hi @v-juanli-msft, yes your understanding is correct. Project Profit is calculated column using formula Gross Revenue - ( Direct Expense + Staff Costs + OHonSubConsultant ). Till now it was not problem, problem was when on this calculated column i tried writing below calculation TOTALYTD (SUM(TableName[Project Profit]), Calendar[Date]), it was returning expected result, as im expecting it to sum Jan + Feb on Feb row. Somehow i managed to get the YTD figures using ALLEXCEPT but now i want to use the numbers as is meaning don't want any aggregation to happen when i select YTD for example i don't want numbers to add up when i select Jan and Feb instead it should sum in respective months only.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors