Multiple countries > Individual row > without affecting grand total

[Anonymous]

Dear community,

I have a simple data set which is composed of:

1. Project ID
2. Country or Countries (separated by a comma)
3. Project Manager name
4. The sales value of the project

In order to ensure that entries with multiple countries where considered, I have followed the instructions as per '

Assigning Multiple Countries to a Value in a Filled Map ' i.e.  split column by delimiter (comma) and unpivot columns. I then renamed the Value and use it to fill the map. That worked.

Imagine my dataset was: 

A0000     UK, France           John Smith         £1

After applying the above methodology, I get:

A0000     UK                        John Smith         £1

A0000     France                  John Smith         £1

However without further intervention, I now got that The PM John Smith has been managing 2 projects and that the total value of his portfolio is £2.

I need to:

1) Ensure that by clicking on John's Smith, both UK and France are filled in the map. As well as, if I click in either UK or France, John's Smith name is given back

2) The column split and row creation would not affect the grand total (i.e. how many projects John is managing) and the cost of each project / John's portfolio.

Thank you so much for your help.

Best wishes,

[Ashish_Mathur]

Hi,

By splitting data into rows, your first question would get answered.  As regards the second one, i can help if you share your PBIX file.

[mwegener]

Hi @Anonymous 

check this out.

PBIX

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Thanks @mwegener  - thank you for your time. I don't have the latest version of Power Bi and I can't open the file. I've asked for an update and I hope it will be installed soon.

Thanks @Ashish_Mathur  - my file is about company sales and I am not allowed to share it, unfortunately. But thank you so much for your offer of help

Any chanche you can still guide me?

[mwegener]

Hi @Anonymous ,

here an overview

Sales Value = SUMX(SUMMARIZE('Table';'Table'[Project ID];"@Sales Value";AVERAGE('Table'[_Sales Value]));[@Sales Value])

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Dear @mwegener , it definitly seems close to work!! ... but on my side, I am still doing something uncorrectly somehow.

I've added this new measure, calling it 'Sales Value MEASURE' (is a measure right?) and I have copied the formula: I named my table and fields exactly as you did, I thought that it would have worked perfectly. But I got a syntax error. 

Sales Value MEASURE = SUMX(SUMMARIZE('Table';'Table'[Project ID];"@Sales Value";AVERAGE('Table'[_Sales Value]));[@Sales Value])

Thank you and sorry if it sounds so silly!

PS. Would that solve also the fact that John Smith is managing ONE project and not two.

Much much appreciated

Best

G

[mwegener · Accepted solution]

Hi @Anonymous ,

I think the syntax error is the use of semicolon (;) instead of comma (,). (Semicolon ist German DAX)

Sales Value MEASURE = SUMX(SUMMARIZE('Table','Table'[Project ID],"@Sales Value",AVERAGE('Table'[_Sales Value])),[@Sales Value])

For the Number of Projects make a distinct count on the Project ID.

Number of Projects = DISTINCTCOUNT('Table'[Project ID])

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Dear @mwegener , thank you so much. It does work!

Have a lovely weekend and thanks again for your patience and help!

[mwegener]

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Dear @mwegener , it definitly seems close to work!! ... but on my side, I am still doing something uncorrectly somehow.

I've added this new measure, calling it 'Sales Value MEASURE' and I have copied the formula: I named my table and fields exactly as you did, I thought that it would have worked perfectly. But I got a syntax error:

Or:

Sales Value MEASURE = SUMX(SUMMARIZE('Table';'Table'[Project ID];"@Sales Value";AVERAGE('Table'[_Sales Value]));[@Sales Value])

Thank you and sorry if it sounds so silly!

Best

G