cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
wiem_ba Frequent Visitor
Frequent Visitor

add a calculated column to a table based on calculations from another related table

I tried to look for an answer to my problem in the community but it seems that all the answers does not really fit to my concerns.

So, i have a table that describes spend of some entities. described by some variables and for 5 years. here's a screenshot of the table 

pbc.PNGspend table

 

 

 

 

 

 

 

market segment column presents a foreign key coming from table market segment that has a list of all the market segments. What i wanna do is create a new calculated column in the table market segment that has the sum of spend from the spend table for each market segment. the table should be like this 

 

 

Market Segment      Spend

Market Seg1            201544

Market Seg2            326595

 

My idea consists of maybe storing each Market Segment from the Market Segment table in a variable var1  and then test in the spend table if spend[Market Segment]= var1 then sum the spend and store it in the new column but since i'm new to pbi I really don't have any idea how to formulate it. I hope I was clear in explaining my issue.

 

PS: I don't wanna do it manually since the market segment table gets updated regularly.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: add a calculated column to a table based on calculations from another related table

Hi @wiem_ba ,

 

If that is the purpose you don't need to add a new column to your tables you can create measures that get the information and calculate the values based on your selections.

 

Calculations in DAX are made based on context meaning that the way you setup your visuals, slicers or make the calculations allow you to get different granularity results.

 

If you want further details on how you can setup this CAGR calculation can you share a sample model the calculation you need and the expected result?

 

If information is sensitive make a mockup model or share it trough private message.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: add a calculated column to a table based on calculations from another related table

Hi @wiem_ba ,

 

You don't need to create a measure for each market as referred DAX is based on context so a variable on your calculations should be the market and when you add it to the visuals the CAGR is correctly calculated based on the selection you have.

You only would have the need of making a new measure for each market if every single one is a column. Based on your image I have a made a very simple model  with the following columns:

 

 

Market Segment Year Spend Categorie
A 2015 500 A1
A 2016 100 A1
A 2017 600 A2
B 2015 300 B1
B 2016 200 B2
B 2017 100 B2
C 2015 400 A1
C 2016 300 A1
C 2017 200 A1

 

I have then created the following measure:

 

CAGR =
VAR Ending_Value =
    CALCULATE (
        SUM ( Spend[Spend] );
        FILTER ( ALLSELECTED ( Spend[Year] ); Spend[Year] = MAX ( Spend[Year] ) )
    )
VAR Beginning_Value =
    CALCULATE (
        SUM ( Spend[Spend] );
        FILTER ( ALLSELECTED ( Spend[Year] ); Spend[Year] = MIN ( Spend[Year] ) )
    )
VAR Number_of_years =
    MAX ( Spend[Year] ) - MIN ( Spend[Year] )
RETURN
    IFERROR (
        (
            (
                DIVIDE ( Ending_Value; Beginning_Value; 0 ) ^ ( 1 / Number_of_years )
            ) - 1
        );
        0
    )

As you can see the calculations is made if you make the market segment only or if you have the category, or market segment and category.

 

CAGR.png

Being a measure this can also have interaction with slicers and filter and another visuals.

 

Only adjustements you may need is if you use dimension tables for dates or for market segment.

 

Check the PBIX file attached (made in last version of PBI June 2019).

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




7 REPLIES 7
Super User
Super User

Re: add a calculated column to a table based on calculations from another related table

Hi @wiem_ba ,

 

Sorry for asking but what is the purpose behind making a new column with the total values per market and then compared it with the value on the other table?

 

If you create a relationship between both tables you can make the calculations directly in your visuals or with measure without the need to add more information to your model.

 

Can you please share some more insights on the purpose and final result you are looking for?

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




wiem_ba Frequent Visitor
Frequent Visitor

Re: add a calculated column to a table based on calculations from another related table

Hi @MFelix 

Sorry I was not clear enough actually the sum of spend for each market segment is going to be just the key for me for another calculation which is the compound annual growth rate for each market segment. So i can use it as a KPI called the fastest growing segment.

 

So, basically the whole idea is to make the calculation of the spend for each segment in the first year (in my case 2015) and the last year (2020) then use it to calculate the CAGR of each segment. afterwards compare the CAGRs and display the segment that has the highest growth. 

 

And i'm trying to make the process as dynamic as possible so that if we update the data and add a new market segment the whole calculation should be done and the new market segment should be taken into consideration in the comparison of the CAGRs. 

 

Thank you for the response

 

Super User
Super User

Re: add a calculated column to a table based on calculations from another related table

Hi @wiem_ba ,

 

If that is the purpose you don't need to add a new column to your tables you can create measures that get the information and calculate the values based on your selections.

 

Calculations in DAX are made based on context meaning that the way you setup your visuals, slicers or make the calculations allow you to get different granularity results.

 

If you want further details on how you can setup this CAGR calculation can you share a sample model the calculation you need and the expected result?

 

If information is sensitive make a mockup model or share it trough private message.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




wiem_ba Frequent Visitor
Frequent Visitor

Re: add a calculated column to a table based on calculations from another related table

@MFelix thank you for your time I already tried to calculate the CAGR based on a calculated measure. 

I guess I will do that for every segment but what I suggest is that power bi add a looping function if possible it would make it very efficient (for or do while loop)

Super User
Super User

Re: add a calculated column to a table based on calculations from another related table

Hi @wiem_ba ,

 

You don't need to create a measure for each market as referred DAX is based on context so a variable on your calculations should be the market and when you add it to the visuals the CAGR is correctly calculated based on the selection you have.

You only would have the need of making a new measure for each market if every single one is a column. Based on your image I have a made a very simple model  with the following columns:

 

 

Market Segment Year Spend Categorie
A 2015 500 A1
A 2016 100 A1
A 2017 600 A2
B 2015 300 B1
B 2016 200 B2
B 2017 100 B2
C 2015 400 A1
C 2016 300 A1
C 2017 200 A1

 

I have then created the following measure:

 

CAGR =
VAR Ending_Value =
    CALCULATE (
        SUM ( Spend[Spend] );
        FILTER ( ALLSELECTED ( Spend[Year] ); Spend[Year] = MAX ( Spend[Year] ) )
    )
VAR Beginning_Value =
    CALCULATE (
        SUM ( Spend[Spend] );
        FILTER ( ALLSELECTED ( Spend[Year] ); Spend[Year] = MIN ( Spend[Year] ) )
    )
VAR Number_of_years =
    MAX ( Spend[Year] ) - MIN ( Spend[Year] )
RETURN
    IFERROR (
        (
            (
                DIVIDE ( Ending_Value; Beginning_Value; 0 ) ^ ( 1 / Number_of_years )
            ) - 1
        );
        0
    )

As you can see the calculations is made if you make the market segment only or if you have the category, or market segment and category.

 

CAGR.png

Being a measure this can also have interaction with slicers and filter and another visuals.

 

Only adjustements you may need is if you use dimension tables for dates or for market segment.

 

Check the PBIX file attached (made in last version of PBI June 2019).

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




wiem_ba Frequent Visitor
Frequent Visitor

Re: add a calculated column to a table based on calculations from another related table

@MFelix much appreciated this is what I wanted to do Smiley Very Happy

Super User
Super User

Re: add a calculated column to a table based on calculations from another related table

@wiem_ba ,

 

as refered this may need some adjustment based on your model but this is the basic setup. If you need any additional assistance tell me.

 

Can you please mark the answer with the measure and the PBIX file as correct one so the other users can see it.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!