topic Re: Subtotals should not sum up values in DAX Commands and Tips

Subtotals should not sum up values

Marcus709 — Thu, 15 Aug 2019 11:25:58 GMT

Hi,

I created a new measure which divides values (Average Kupon ist the goal).

On the subtotal level, all avg kupons of the individual rows which go into the subtotal will be added.

This is not wanted. The subtotal should use the same calculation as described for the individual positions.

How is that done?

For a better illustration the current formula:

Akt Kupon = (SP[Kupon]*SP[Marktwert adj/Börsenwert.]/SP[Marktwert adj/Börsenwert.])

Kind regards

Marcus

Re: Subtotals should not sum up values

Anonymous — Thu, 15 Aug 2019 11:34:18 GMT

Completely unclear. Please state your problem in understandable terms so that anyone can analyze and help you.

Thanks.

Best
Darek

Re: Subtotals should not sum up values

Marcus709 — Thu, 15 Aug 2019 14:18:09 GMT

Hi darlove, I´ll try my best.

Ok. I have a table called "SP"

Column "Kupon" means coupons of a bond, say 4%

Marktwert adj./Börsenwert is the marketvalue, say 1,000,000

The mearure is called Akt Kupon:

Akt Kupon = (SP[Kupon]*SP[Marktwert adj/Börsenwert.]/SP[Marktwert adj/Börsenwert.])

So it divides the product of coupon times marketvalue ba the marketvalue. Now you´ll ask me if I´m stupid to do so, since coupon is alwas the result on a single position level.

But I use this formula especially for the subtotal level. There are sevarasl goups of bonds. Each category has a subtotal. Lets make a simple example:

Bond1; coupon 4%; 1,000,000 marketvalue

Bond2; coupon 3%; 1,000,000 marketvalue

Subtotal: coupon 7%, 2,000,000 marketvalue (thats what the formula currently delivers)

But what it should do is display the sumproduct. That means, the subtotal should display:

Subtotal: coupon 3,5%; 2,000,000 marketvalue

In my earlier used excel pivot table it does what it should but not in BI.

Can you give me a hint?

Thanks a lot

Marcus

Re: Subtotals should not sum up values

Anonymous — Thu, 15 Aug 2019 14:40:58 GMT

First off, mate, this

Akt Kupon = (SP[Kupon]*SP[Marktwert adj/Börsenwert.]/SP[Marktwert adj/Börsenwert.])

can't be a measure. A measure has to have some kind of aggregation in its definition. This does not, hence it's not a measure.

Secondly, this

SP[Marktwert adj/Börsenwert.]/SP[Marktwert adj/Börsenwert.]

will always return 1. Can't understand why you'd do such a silly thing...

Thirdly, I want to see the structure of your model. If you want to calculate something on the fly - which means you need a measure - then you calculate over something. This something is the rows that hide behind your slicers and are stored in your fact table.

I do understand what you want to calculate but I want to understand the model to calculate it correctly.

Best
Darek

Re: Subtotals should not sum up values

Anonymous — Thu, 15 Aug 2019 14:43:16 GMT

Also, it would be desirable to see your pivot in Excel to see what you want to achieve. Can you paste some snapshot?

Best
Darek

Re: Subtotals should not sum up values

Marcus709 — Thu, 15 Aug 2019 15:05:34 GMT

no, I won´t pass on this is company stuff. My simple explanation should really explain what I want.

So I will look for another forum if you can´t get may point, no problem.

The world is full of competent and friendly people

Best

Marcus

Re: Subtotals should not sum up values

Anonymous — Thu, 15 Aug 2019 17:34:33 GMT

Thanks. I'm incompetent and unfriendly :) Glad to hear that. Well, I did want to help you but I can see you don't understand my point. So be it. Let others try.

Best
D.