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

[Anonymous]

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 

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

[MFelix · Accepted solution]

Hi @Anonymous ,

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

[MFelix]

Hi @Anonymous ,

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

[Anonymous]

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

[MFelix · Accepted solution]

Hi @Anonymous ,

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

[Anonymous]

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

[MFelix · Accepted solution]

Hi @Anonymous ,

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.

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

[Anonymous]

@MFelix much appreciated this is what I wanted to do 

[MFelix]

@Anonymous ,

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