no calculation vs running
Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Excel Training and help » No Calculation vs Running Total in Power Pivot Table

No Calculation vs Running Total in Power Pivot Table

resolvedResolved · High Priority · Version 2013

Tsudoi has attended:
Excel PowerPivot course
Excel VBA Introduction course

No Calculation vs Running Total in Power Pivot Table

Hi,
It's hard to explain the issue I have but hope you can understand.
I could send a sample file if needed, so please let me know.

I have a pivot table from data model with following calculated field in one column.

=if([Bud Orders Sales]>[YTD Orders Sales],[YTD Orders Sales],[Bud Orders Sales])

And, I have Bud Orders Sales in other column and YTD Orders Sales in other column in the same pivot as measure.

The formula itself works fine but the result does not show how I expected when the values are shown in Running Total.
It seems ok when in No Calculation.

The result does not equal to either values (Bud or YTD) and I am scratching my head as to why that is..

Hope it makes sense. Please do let me know if you'd need me to send you a sample data.

Regards
Thanks,

RE: No Calculation vs Running Total in Power Pivot Table

Hi Tsudoi,

Thank you for the forum question.

You will need to create measures in the data model to get a correct running total in you PivotTable.

If you have a look at the link below, you will find a very good example of how to create the measure.

https://javierguillen.wordpress.com/2012/11/28/running-total-techniques-in-dax/



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: No Calculation vs Running Total in Power Pivot Table

Hi Jens,

Thanks for your reply.
As the link to the sample download is not available and I really don't understand the examples on the site, is there anyway you can take a look at my case?
I ask this because when I use Running Total from the pivot for my normal sales total measures (Bud & YTD), it works fine and all I am doing is to use these measures to do a simple IF function. So why not work?

Thanks,

RE: No Calculation vs Running Total in Power Pivot Table

Hi Tsudoi,

I am sorry but it is not inside the scope of this forum to analyse our clients data models.

But by looking at your if without seeing the model I cannot understand how you should be able to compare the running total.

The If will take the value from the column with the lowest value and the running sum of this cannot be compared with BUD or Sales.

=if([Bud Orders Sales]>[YTD Orders Sales],[YTD Orders Sales],[Bud Orders Sales])


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: No Calculation vs Running Total in Power Pivot Table

Hi Jens,
Thanks again for your reply.
I think I may be fundamentally wrong trying to compare something that's not possible.
So disregarding the way I've tried, the question is how to do it correctly.

So, simply I have a table as follows from data model.

M5-M16, Month (in 1 to 12)

N5-N16, Bud Sales in Running Total (this is measure created with Calculate)

O5-O16, YTD Sales Sales in Running Total (this is measure created with Calculate)

P5-P16, Bud vs YTD (this is measure created [YTD Orders Sales]-[Bud Orders Sales])

Q5-Q16, this is where I want to do the IF where I want to show, if N5>O5, then show O5, or else show N5.

If that measure, if([Bud Orders Sales]>[YTD Orders Sales],[YTD Orders Sales],[Bud Orders Sales]), is not the right way to do it, what's the right way to do it?

How can I achieve this? What are the ways to do it please?

Thanks,
Regards






RE: No Calculation vs Running Total in Power Pivot Table

Hi Tsudoi,

I am sorry but I cannot understand what you want to achieve with the IF. You want a column which display the smallest value from
[Bud Orders Sales] or [YTD Orders Sales].

If you want this it is the right way to do it, but I cannot see, how you can expect to compare this to Bud or YTD.




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: No Calculation vs Running Total in Power Pivot Table

Hi Jens,

One might think it's the right way to do but it does not work.
I feel like I am fundamentally misunderstanding how DAX works, hence my question here.

I don't have Bud sales in one colunm and YTD in another. I have the both in the same coloum and so I show them seprately in the pivot table by CALCULATE formula as follows.

Bud Orders Sales:=CALCULATE(sum('Order'[£Orders]),'Order'[Type]="Budget v2",'Order'[Years]=2018)/1000

I do the same for YTD as follows.

CALCULATE(sum('Order'[£Orders]),'Order'[Type]="YTD",'Order'[Years]=2018)/1000

And for P5-P16, I have simple Bud vs YTD as follows, which works fine.

YTD Orders Sales]-[Bud Orders Sales]


Then for Q5-Q16 the IF formula returns correct result for Jan but incorrect from Feb to Dec.

I've looked at it many times but cannot figure out why the IF formula does not work.
Hope that makes sense?

Regards
Thanks,


Edited on Sun 20 May 2018, 11:23

RE: No Calculation vs Running Total in Power Pivot Table

Hi Tsudoi,

Could you please let me know what you you want to achieve with the If.

It doesn't really matter if you have it in one column or two columns. The If function will return the lowest value from the two measures.

On our PowerPivot course we have Introduction to DAX. Multi dimension calculations in data models are very complicated. I cannot help you getting this knowledge, but you can find books and websites, where you can study this.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: No Calculation vs Running Total in Power Pivot Table

Hi Jens,

Thanks for your reply.
I hope I can send you the sample file so that it's a lot easier to understand but I will try again here to explain the best I can.


So again, I have a simple table in Excel from data model.


M5-M16, Month (in number 1 to 12)


N5-N16, Bud Sales shown in Running Total with following formula.
CALCULATE(sum('Order'[£Orders]),'Order'[Type]="Budget v2",'Order'[Years]=2018)/1000


O5-O16, YTD Sales Sales in Running Total with following formula.
CALCULATE(sum('Order'[£Orders]),'Order'[Type]="YTD",'Order'[Years]=2018)/1000


P5-P16, I have simple comparison Bud vs YTD with following formula, which works fine.
[YTD Orders Sales]-[Bud Orders Sales]


Q5-Q16, this is where I want to achieve the IF function where I want to show, if N5>O5, then show O5, or else show N5 throgh to row 16 in the same Excel table with following formula.
If([Bud Orders Sales]>[YTD Orders Sales],[YTD Orders Sales],[Bud Orders Sales])


In other words, if Bud is greater than YTD, then show YTD, otherwise show Bud. So I want to compare column N and O and show smaller value.And, this is the formula that does not work.

I hope that makes sense.

Thanks,
Regards

RE: No Calculation vs Running Total in Power Pivot Table

Hi Tsudoi,

I am sorry, but I cannot help you with this one. Data models and data cubes calculations are based on how the data model is created. How the tables are related and where the calculations are made.

I will need to analyse your data model and it is outside the scope of this forum.

If the If do not return any values you can recognise the way you think your data model may be wrong. Sometimes the Calculate function need nested Filter functions to get the data grouped correctly in the PivoTable. There can be a lot of reasons for the If function to returm wrong info.

I am sorry but I hope that you will find a solution.




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Edited on Sun 20 May 2018, 16:31

RE: No Calculation vs Running Total in Power Pivot Table

Hi Tsudoi,

I am sorry, but I cannot help you with this one. Data models and data cubes calculations are based on how the data model is created. How the tables are related and where the calculations are made.

I will need to analyse your data model and it is outside the scope of this forum.

If the If do not return any values you can recognise the way you think your data model may be wrong. Sometimes the Calculate function need nested Filter functions to get the data grouped correctly in the PivotTable. There can be a lot of reasons for the If function to returm wrong info.

I am sorry but I hope that you will find a solution.




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: No Calculation vs Running Total in Power Pivot Table

Hi Jens,

I know the issue is a very simple one and I am sutr I am making it more complicated than it actually is because I am trying to explain here having to write up formulas without the whole picture. I wish I can show you the table in the sample file like the last time. It'll show you what I am talking about straight away. I am not asking for analysis but only how to achieve something that's to do with simple power pivot table.
I've contacted you here after trying to resolve it on my own many times, so I can't come to solution if I get no help here.

Regards
Thanks

RE: No Calculation vs Running Total in Power Pivot Table

Hi Tsudoi,


I am sorry but I cannot help you with this one. Your If should work if your data model and DAX calculations are right. Otherwise you have a problem with your data model or your understanding of data cubes.

I will need to analyse your data model to find out why your If is not returning the right answer. I can ask our sales team to contact you and we can do it as a consultancy.




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: No Calculation vs Running Total in Power Pivot Table

Hi Jens,

Maybe I am not asking the right question to clarify where the issue lies. So, looking differently, how would we do MIN function across columns?
I don't have to do the IF function, alternatively MIN does the job but across two columns, not rows.

So, I have Bud in column N in Running Total and YTD in Running Total and in column O (both from measures which I know works correctly).
Simply, taken the smaller value by MIN for each month.

How would we do that please?

Thanks,
Regards


RE: No Calculation vs Running Total in Power Pivot Table

Hi Tsudoi,


In the measure area =min([Bud Orders Sales],[YTD Orders Sales])

I guess this will return the same as your If, when you add the measure to your PivotTable.

It can be a problem that you are not using a separated date table but reference the year in the orders table. And the same in the PivotTables it should be grouped by the Year from a date table not from the transaction table.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: No Calculation vs Running Total in Power Pivot Table

Hi,
I don't have Date table nor Order table. I have everything in one table, no relations anywhere.
And, min([Bud Orders Sales],[YTD Orders Sales]) returns error in data model. I've tried this already. This would work if it was in normal table by MIN(N5:O5) but N column and O column both have a measure and it doesn't work...

This is a more general question (not specif to my case here) to ask, how to do MIN function across multiple columns that are from measure in power pivot.

Thanks,
Regars

RE: No Calculation vs Running Total in Power Pivot Table

Hi Tsudoi,

I was a little bit to fast with my last answer, but the syntax should be right in the DAX below.




= IF(MIN('Table1'[Column1])<=MIN('Table1'[Column2]),MIN('Table1'[Column1]),MIN('Table1'[Column2]))

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: No Calculation vs Running Total in Power Pivot Table

Hi Jens,

Thanks again.
The syntax would work I am sure but I am talking about case where column is a measure.
So when I tried to do a simple MIN(measure instead of 'Table'[column]), this returns error already.

So the question is, how to do MIN function across multiple columns that are from measure in power pivot?

Thanks,
Regards


RE: No Calculation vs Running Total in Power Pivot Table

Hi Tsudoi,

I have tested the syntax in a data model from calculated columns and the syntax is working fine in my model based on one table as yours.

You must have another problem with you Data Model.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Tue 29 May 2018: Automatically marked as resolved.

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

See also:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel articles

Please browse our web site to find out more about
excel training microsoft and other training courses.

Excel tip:

Quick Zooming in Excel with rollerball mouses

To zoom in and out of your page hold down the control key and roll the wheel up and down. This will zoom up and down 15% at a time.

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.07 secs.