cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Marcus709 Frequent Visitor
Frequent Visitor

Subtotals should not sum up values

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

6 REPLIES 6
Super User
Super User

Re: Subtotals should not sum up values

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

Thanks.

Best
Darek
Marcus709 Frequent Visitor
Frequent Visitor

Re: Subtotals should not sum up values

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
 

 

 

Super User
Super User

Re: Subtotals should not sum up values

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
Super User
Super User

Re: Subtotals should not sum up values

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
Marcus709 Frequent Visitor
Frequent Visitor

Re: Subtotals should not sum up values

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

Super User
Super User

Re: Subtotals should not sum up values

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.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 304 members 2,942 guests
Please welcome our newest community members: