Three columns names - sum the names every time the same name shows up

[mluanastevens]

Hi I am new business Analyst and I cant seem to find the help for this question.

I basically have a large dataset with few columns named by diferent levels with one column with Cash. Like a Piramid type thing.

I basically need to have the total by column, knowing that:

1) Top person needs to sum all the specific column names ( like: Jessica Top person total should be $150, Vanessa should be $80,....) 2) 3Level totals should be the sum of the 2nd level +3level+ 1 level

3) 2nd level totals needs to be 2nd level+1level

I hope to find help for this. I am really excited for this project.

[v-yalanwu-msft · Accepted solution]

Hi, @mluanastevens ;

You could create a table as a name list. then create a measure.

1.create a table.

name = 
var _a=SUMMARIZE('Table',[Cas Name])
var _b=SUMMARIZE('Table',[GAName])
var _c=SUMMARIZE('Table',[MGAName])
var _d=SUMMARIZE('Table',[RGAName])
return DISTINCT( UNION(_a,_b,_c,_d))

2.create a measure.

MGA = IF(ISINSCOPE('name'[Name]),CALCULATE(SUM([ALP]),FILTER(ALLSELECTED('Table'),[MGAName]=MAX('name'[Name]))),SUM([ALP]))

RGA = IF(ISINSCOPE('name'[Name]),[MGA]+CALCULATE(SUM('Table'[ALP]),FILTER(ALLSELECTED('Table'),[RGAName]=MAX('name'[Name]))),[MGA]*2)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft · Accepted solution]

Hi， @mluanastevens ；

Try it.

MGA = IF(ISINSCOPE('name'[Name]),CALCULATE(SUM([ALP]),FILTER('Table',[MGAName]=MAX('name'[Name]))),SUM([ALP]))

MGA = IF(ISINSCOPE('name'[Name]),CALCULATE(SUM([ALP]),FILTER('Table',[MGAName]=MAX('name'[Name]))),SUM([ALP]))

Best Regards,
Community Support Team_ Yalan Wu

[v-yalanwu-msft]

Hi, @mluanastevens ；

Is your problem solved? If so, Would you mind accept the helpful replies as solutions? Then we could close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,

Community Support Team_ Yalan Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @mluanastevens ;

You could create measures as follows:

Top per_sum = 
 CALCULATE(SUM([Cash]),FILTER(ALL('Table'),[Top Person]=MAX('Table'[Top Person])))

level2 = 
var _level1= CALCULATE(SUM([Cash]),FILTER(ALL('Table'),[1Level]=MAX('Table'[Top Person])))
var _level2= CALCULATE(SUM([Cash]),FILTER(ALL('Table'),[2nd Level]=MAX('Table'[Top Person])))
return _level1+_level2

level3 = 
var _level1= CALCULATE(SUM([Cash]),FILTER(ALL('Table'),[1Level]=MAX('Table'[Top Person])))
var _level2= CALCULATE(SUM([Cash]),FILTER(ALL('Table'),[2nd Level]=MAX('Table'[Top Person])))
return _level1+_level2+ CALCULATE(SUM([Cash]),FILTER(ALL('Table'),[3level]=MAX('Table'[Top Person])))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mluanastevens]

Hi, thank you so much for you help. I feel I am on the right track here, but can you help me why I keep getting the same totals for everybody?

[v-yalanwu-msft]

Hi, @mluanastevens ;

I'm really sorry that the details you gave me are too few,  so I can't judge the reason. If possible, could you share a simple file similar to your data? Or delete sensitive information and share it?
Best Regards,
Community Support Team_ Yalan Wu

[mluanastevens]

https://docs.google.com/spreadsheets/d/1pNTfmaKBQ2qNOIGruOyVRV3u8AZYE8m3jw02YwqzlUk/edit?usp=sharing Here is the link for the data 

[mluanastevens]

Hi @v-yalanwu-msft Here is a snip of my data,

Cas Name	ALP	SAName	GAName	MGAName	RGAName	TP
SHAWN GIRDHARI	$300	SHAHRIAR MOHAMMADI	0	AMIR SEDAGHAT	GUJRAL AGENCY LTD	Bob Gujral
WALIMUNIVIRAJ ABEYSEKERA	$300	0	0	AMIR SEDAGHAT	GUJRAL AGENCY LTD	Bob Gujral
MOHINI LALL	$117	JILLIAN ARSHADI	0	GUJRAL AGENCY LTD	0	Bob Gujral
DEVIN JENKINS	$61	ROBERTO SAMANODIAZ	0	JACQUELINE KABLUYEN	0	Jackie Kabluyen
DEMETRIUS THOMAS	NULL	TIFFANY ARNTZEN	VANVIPHA SUGAI	KEVIN APPASAMY	0	Kevin Appasamy
DEMETRIUS THOMAS	$1,147	TIFFANY ARNTZEN	VANVIPHA SUGAI	KEVIN APPASAMY	0	Kevin Appasamy

I usualy put it in pivot table so I can basically see the Top Person total, MGA total and RGA (which is MGA+RGA combined, I basically stack one on top of each other and re pivot to get the RGA total)

MGA and Top Person are easy to do on Power BI, i can just put them on filters, but the RGA (MGA+RGA) are the tricky part.I tried doing what you told me above and its not working, I keep getting the same numbers for everybody.

So basically MGA total will be:

Row LabelsSum of ALPGrand Total1925.16

AMIR SEDAGHAT	600
GUJRAL AGENCY LTD	117.48
JACQUELINE KABLUYEN	60.6
KEVIN APPASAMY	1147.08

RGA total will be :

0	1325.16
AMIR SEDAGHAT	600
GUJRAL AGENCY LTD	717.48
JACQUELINE KABLUYEN	60.6
KEVIN APPASAMY	1147.08

Do you think you can help be?

Hope it make sense. I really appreciate your help.  Thank you.

[v-yalanwu-msft · Accepted solution]

Hi, @mluanastevens ;

You could create a table as a name list. then create a measure.

1.create a table.

name = 
var _a=SUMMARIZE('Table',[Cas Name])
var _b=SUMMARIZE('Table',[GAName])
var _c=SUMMARIZE('Table',[MGAName])
var _d=SUMMARIZE('Table',[RGAName])
return DISTINCT( UNION(_a,_b,_c,_d))

2.create a measure.

MGA = IF(ISINSCOPE('name'[Name]),CALCULATE(SUM([ALP]),FILTER(ALLSELECTED('Table'),[MGAName]=MAX('name'[Name]))),SUM([ALP]))

RGA = IF(ISINSCOPE('name'[Name]),[MGA]+CALCULATE(SUM('Table'[ALP]),FILTER(ALLSELECTED('Table'),[RGAName]=MAX('name'[Name]))),[MGA]*2)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mluanastevens]

I have weeks as filters as my data goes back till week 1 of 2021. When I have just one week selected the number is right, but when I select more than one week,  the numbers are wrong:

Here is a example. Do you know what can be causing this?

[ryan_mayu]

@mluanastevens 

pls see the attachment and check if this is what you want.