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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Syndicate_Admin
Administrator
Administrator

Calculated column subtracting Real vs Budget

Good, I'm new to the community. I'm putting together a table from a database I have where there are rows that correspond to actual periods and rows to budget periods. I show you an example:

PeriodFrequencyIncomeCostResult
Real2020014060
Real2020014060
Real2020014060
Real2020014060
Real2020014060
Budget3020012575
Budget3020012575
Budget3020012575
Budget3020012575

I would need to put together a summary table where the real and the budget are exposed as columns, and have a 3rd column that compares me these 2 periods:

ValueRealBudgetDif. Real vs Budget
Frequency100120-20
Income1000800200
Cost700500200
Result3003000

I do not know how to generate a calculated column that basically subtracts the real values vs the budget values, but without being associated with a specific field, I would need this calculation to apply to all the fields that the original table has. I hope I have been clear.

Thank you very much for your help

Santiago

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @SantiPeri ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Unpivot the selected columns:Frequency, Income, Cost and Result in Power Query Editor

yingyinr_0-1633411797233.png

2. Create a measure as below

Dif. Real vs Budget = 
VAR _diff =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Period] = "Real" )
        - CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Period] = "Budget" )
RETURN
    IF ( ISINSCOPE ( 'Table'[Period] ), SUM ( 'Table'[Value] ), _diff )

3. Create a matrix visual(Rows: Type   Columns: Period   Values: Dif. Real vs Budget)

yingyinr_1-1633412081282.png

Best Regards

Community Support Team _ Rena
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

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

Hi @SantiPeri ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Unpivot the selected columns:Frequency, Income, Cost and Result in Power Query Editor

yingyinr_0-1633411797233.png

2. Create a measure as below

Dif. Real vs Budget = 
VAR _diff =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Period] = "Real" )
        - CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Period] = "Budget" )
RETURN
    IF ( ISINSCOPE ( 'Table'[Period] ), SUM ( 'Table'[Value] ), _diff )

3. Create a matrix visual(Rows: Type   Columns: Period   Values: Dif. Real vs Budget)

yingyinr_1-1633412081282.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@Syndicate_Admin , group by and  Transpose the data in power query and try

Transpose: https://yodalearning.com/tutorials/power-query-helps-transposing-data/

 

Then you can create a measure of diff

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors