- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- add a calculated column to a table based on calcul...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

wiem_ba

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-14-2019
09:32 AM

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

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.

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

Accepted Solutions

MFelix

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-14-2019
10:50 AM

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

Proud to be a Datanaut!

MFelix

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-15-2019
06:44 AM

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.

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

Proud to be a Datanaut!

7 REPLIES 7

MFelix

Super User

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-14-2019
10:25 AM

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

Proud to be a Datanaut!

wiem_ba

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-14-2019
10:37 AM

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

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-14-2019
10:50 AM

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

Proud to be a Datanaut!

wiem_ba

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-15-2019
01:07 AM

@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

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-15-2019
06:44 AM

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.

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

Proud to be a Datanaut!

wiem_ba

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-15-2019
07:01 AM

@MFelix much appreciated this is what I wanted to do

MFelix

Super User

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-15-2019
07:23 AM

@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

Proud to be a Datanaut!