Calculate 1 row value difference from next level up in hiearchy level value

[Anonymous]

Hello All,

Without adding the pbix or sample data I am hoping from this screenshot you can help me figure this out.

For the "Sales Market Share" column I want to calculate the 1.7% row difference from the 1.3% that is the next level up in hiearchy that would equal .4 (do not confuse the column called "Sales Market Share Pt Chg" as the .4 I am referring to) and then multiple the .4 times the column of "$ Value of 1 Share Point" column value of $183,294 and come up with an answer of $73,317

IN this example the 1.7% row is for a dimension called Segment and the 1.3% row is for a dimension called SubCategory.

Hoping this is enough information to have you help me. 

[amitchandak]

@Anonymous , Try a new measure like this

calculate([maerket share], allexcept(Table, Table[SubCategory], table[Segment])) -calculate([maerket share], allexcept(Table, table[SubCategory]))

[Greg_Deckler]

@Anonymous - It is not, you left out what columns make up your hierarchy as well as how that hierarchy is defined (as a hierarchy, ad-hoc hierarchy, ? You mention dimensions so are those coming from other tables or ss your table like

Segment, SubCategory, Value1,Value2,Value3...

Without sample data and more information all I can tell you is that you will need to take the current value of the cell and then you will need to determine it's parent. With that information you can SUMMARIZE(ALL()...) at the level of the parent then filter down to the parent you want. It's a measures aggregation thing. This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

But that's about all I can tell you without some amount of effort going into the post on your part.

[Anonymous]

Hi @Greg_Deckler 

Good point and I was thinking I didn't provide enough details.

The hiearchary is actually in rows and a true defined one, and it is a specific hiearchy that goes:

Sub Category

Segment

Sub Segment

Size Group

Variety

Item Description

UPC

[Greg_Deckler · Accepted solution]

@Anonymous - OK, I'm going to get on my soapbox for a moment. I will never understand why those asking for help will not put forth any effort to mock up some data and post it as text so that those trying to help don't have to spend a bunch of time mocking up data, which will likely be all wrong to begin with just to be able to try to assist someone that doesn't really seem to want to be helped in any real urgent capacity such that they put forth the minimal amount of effort.

OK, I am off my soap box now. My apologies, you caught me at one of those moments. In any case, I did put forth the effort to attempt to mock up your data and test out a solution. I don't usually do that but I was bored. Anyway, it is attached as a PBIX file below sig. It is basically this:

Measure 3 = 
    VAR __MarketShare = MAX('Table (17)'[MarketShare])
    VAR __Segment = MAX('Table (17)'[Segment])
    VAR __Table = SUMMARIZE(ALL('Table (17)'),[Segment],"MS",AVERAGE([MarketShare]))
    VAR __SegmentMS = MAXX(FILTER(__Table,[Segment] = __Segment),[MS])
    VAR __Diff = __MarketShare - __SegmentMS
RETURN
    __Diff * 100 * SUM([Value])

 You want Table 17 and Measure 3 and Page 17.

[Anonymous]

hI @Greg_Deckler 

Appreciate your directness.....

Let me remind you that back in the day you were a novice much like me who is trying to get up to speed on PowerBI, unless you were born with a gifted set of all the answers to PowerBI, I for one was not.

I however am someone new who grew up on Excel and then Tableau and am trying to learn something new.

So if you think I currently know how to "mock up data" or whatever you would like, then you have forgotten your beginnnings of learning a new language/program.  I would love to know the rules of this forum but I don't see any posted.

I thought this post was for "help" not condemnation!

I am on this forum for help from experts like you, who obvisously have several years head start than I do.

You want to help me great, help me. If you want to overlook us newbies please move on to the next post that satisfies your need.

All of this being said, I do appreciate you putting your response in two different tones. Yes, you are frustrated because I am a newbie who doesn't barely understand what i am asking but thank you for removing your soap box and trying to answer my question.

Please help me ask the question in a way that people can help me. That is all that us newbies are trying to do in order to get to your level.

Respectifully,

Greg

You want to condemn me because I am not at your level yet than so be it....don't worry about responding to this trivial post if that is how you feel. As one of the top leaders of this post I would expect something more from a leader.

[Greg_Deckler]

@Anonymous - Not condeming you because you are not at my level. Open Excel, type some data, select that data, paste into a forum post. Seriously, takes no skill what-so-ever. I opened Power BI, Enter Data query, typed some data. Enter data query, typing data, right-click, copy, paste into forum post. Takes zero skill in DAX or pretty much anything else. Just mentioning it. 

Point is, if you want help, put forth just the bare minimum of effort to deserve someone spending their nights and weekends to help you. That's it. I don't normally complain about it but for some reason you acknowledging the fact that you knew the right thing to do and were still not going to do it and then trying to justify it set me off. But, as much as you want to condemn me for "condeming" you and then apologizing and still doing the work you should have done and then giving you an example of how to solve your issue... Well anyway, whatever makes you feel good about yourself, it's all sunshine and rainbows after all.

[Anonymous]

@Greg_Deckler 

Good response and thank you....

I consider myself a fairly technical person and Excel expert.

However, when it comes to BI I have 7 tables (Maybe queries, I am not sure at this moment; 3 are dim tables and 4 are fct tables)

So I am not sure that you say to open excel, type some data, select the data and paste into the forum.

Again, I know Excel like the back of my hand but when it comes to BI or DAX everybody on this forum acts like we should know what the heck we are doing. This is not my first post to the community and maybe I should be posting in a kindergarten section of this forum. But again, I am advanced at Excel with a mid term level of SQL or DAX (very beginning) so any help users can give me to understand what you are asking would be greatly appreciated. We are only trying to get to your levels!

Thanks! 

[Greg_Deckler]

@Anonymous - Sorry if the community comes across like that, I'm sure that's not the intent but it is very possible that folks that have been at this for 4 or 5 years have forgotten what it is like starting out. I have tried to post some things to help jump start people getting started, namely:

Before you Post Read This: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

And How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Again, I want to apologize for lecturing, I don't normally do that. It's a new day and I am in a better mood than last night! 🙂

[Anonymous]

No worries @Greg_Deckler 

WIth a name like Greg you can't be all bad!

Thanks,

Greg Mooney