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

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
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

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

Anonymous
Not applicable

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