How do you calculate a field from calculated fields.

[alex1alex]

Hello everyone,

I need to Aggregate a number of multiplications which are based on the Row and Columns. My best attempt at describing this is in pseudo-code.

[FONT=Calibri] 
[/FONT][FONT=Calibri]For each cell in the Pivot table[/FONT]
[FONT=Calibri]    SUM[/FONT]
[FONT=Calibri]       Foreach ORU [/FONT]
[FONT=Calibri]                  Percent= Look up the multiplier for that ORU associated with the Column[/FONT]
[FONT=Calibri]                  SUMofValue = Add all of the Values associated with that Column/Row combination[/FONT]
[FONT=Calibri]                  Multiply Percent * SUMofValue
[/FONT]

I tried a number of ways over the last few days and looked at loads of examples but am missing something.


I have an example XLS here from my onedrive: link
With some screen grabs below.
At the bottom is what I'd like the pivot table to look like.

Any help would really be appreciated.

Kind Regards,
Alex

 

[alex1alex]

Looking at this some more....the hurdle is that I want to use the context of the column as part of my calculated column. So, in my example, MGR is the column, but I have no way to look up that column for the filter (which I would then use to lookup the percent in the ORUBUMGR table).

Can you use/lookup the column context?

 

[ImkeF]

Hi Alex,
have a look here: A mystifying and awesome solution for many 2 many « PowerPivotPro
It will be measures instead of calc columns.

 

[alex1alex]

I have to admit, that's a cool page and I'll be using that!

But for this question....I think I have a 1:0 relationship (I don't even know what to call it...maybe that's my problem.)

If you look in the data model, the MGR only shows up in one data table...and i put it in the Column of the pivot table.
For the calculated item, I need to say, "look up my MGR, and get the associated Percent"
(then do some multiplication)

The Magic measure seems to require that the MGR be in a couple of tables.... NO?

I appreciate the help!

 

[ImkeF]

Well, yes model looks a bit strange, but at least in my eyes you have a m2m-relation between charges and ORUBUMGR, with the necessary Bridge table (ORUMAP) in between.

So sth like: CALCULATE(SUM(ORUBUMGR[Percent]), ORUMAP)*CALCULATE(SUM(Charges[Value]), ORUMAP) should give the desired result.

But having a look at it: only if you actually use the ORU field in your Pivot Report (you need that filter context). That's at least not the case in your example - so unfortunately this might not work here - sorry.

 

[alex1alex]

Yes, that was my 1st try as well but the operator order is wrong.

I need a:
SUM ( Percent * Value )
where as that example is doing:
SUM ( Percent ) * SUM (Value )

To be clear, I do use the ORU field I the Pivot Report but as part of the calculated field. (which we're talking about....if that makes sense.)
Regarding the m2m, yeah, I see what you mean...the ORU is M2M with the bridge....it's the MGR that is the 0-to-1

Thanks for you attempt

 

[ImkeF]

Hi Alex,
This is really weird, but it works - let's hope that a merciful DAX expert catches this and tidies up

Everything happens in ORUBUMGR:

1) Create a calculated column: =[ORU]&[MGR]

Giving you the desired granularitiy-level for your %-values (line-basis, but you need a field that can be referenced, probably a simple index would work as well).

2) Create measure: MValue:=CALCULATE(SUM(Charges[Value]); 'ORUMAP')

Here you apply the m2m-technique to retrieve the Value from the Charges-table

3) Create measure: %:=CALCULATE(SUM([Percent]); FILTER(ALL(ORUBUMGR); COUNTROWS(FILTER(ORUBUMGR; EARLIER(ORUBUMGR[level])=ORUBUMGR[level]))))

This is the weird part: We’re forcing that line-level to stick before we aggregate, even on aggregates levels (as we want to have it this way: SUM ( Percent * Value ) by applying a grouping-technique (on the single line….shame - I’m not that advanced on DAX actually – let’s hope that someone tidies this up…)

4) Create measure: Result:=SUMX(ORUBUMGR; [MValue]*[%])

This is your Sumproduct (SUM ( Percent * Value ) instead of SUM ( Percent ) * SUM (Value))

 

[alex1alex]

Hi!

Um. Yeah. It works! I'm beyond excited. If only I understood it

To clarify, for 1), the calculated column is named [level] { as that's what's referenced in the filter for 3) }

I run across the EARLIER function while looking for a solution but was at a loss as to how I would implement it.

Thanks SO Much!

Kind Regards,
Alex

PS: and that you can use "%" as a name, that's just cool. I've learned a lot from this thread

 

[ImkeF]

Hi Alex,
glad you figured „level“ out.

Still think that's overkill/the wrong way around, just grabbed the idea from a previous post, where you’ll also find the link to how PREVIOUS works in a measure: http://www.mrexcel.com/forum/power-bi/847358-dax-average-different-level.html

Take care,

 

[ImkeF]

Hi Alex,
These fellows brought nested SUMX into play which gets us rid of the necessity for that additional column:
http://www.mrexcel.com/forum/power-bi/847993-dax-question-weighted-average-across-granularities.html

Combined with some other wisdoms shared in the net (thank you ) that are elaborately explained here:
MDX and DAX topics: The Logic behind the Magic of DAX Cross Table Filtering (explaining how calculate helps keeping the context)
– we actually end up with a single measure, that looks halfway decent:

ResultS:=SUMX(ORUBUMGR; Calculate(SUMX(ORUBUMGR;[Percent]))*CALCULATE(AVERAGEX(ORUBUMGR;CALCULATE(SUM(Charges[Value]);'ORUMAP'))))

It’s getting into the right direction now, I think