How to calculate 2 values from the same column

Anything related to PowerPivot and DAX Formuale
Jam
Posts: 3
Joined: Fri Dec 08, 2017 2:28 am

How to calculate 2 values from the same column

Hi all,

I've got a unique table like that:
Capture.PNG (7.48 KiB) Viewed 350 times

and I'd like to transform it like that using PowerPivot:
CapturePivot.PNG (6.17 KiB) Viewed 350 times

My question: is it possible to create the calculation column with this (or another) formula and how ? Do I have to use Dax formula and which one ? Do I have to create a temp table before I create the Pivot ?
Well, I'm a bit lost as I'm quite new to this. If someone could give me help it would be nice.

MattAllington
Posts: 919
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: How to calculate 2 values from the same column

This should be standard dax. Load the original table as is. Set up a new table like your table 2. The formula for the measure is

Raw=sum(tablename[act])
Final=Divide(calculate([raw],tablename[nb_na]=“recette”),calculate([raw],tablename[nb_na]=“frequentation”)
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

Jam
Posts: 3
Joined: Fri Dec 08, 2017 2:28 am

Re: How to calculate 2 values from the same column

Hi Matt,

Thank you very much for your help.

I succeed in creating the Measure, there's just two little things I'd like to know (or understand):
What do you mean by "set up a new table like your table 2" ?
And - but this is may be connected to the previous point - is it possible to have the data presented as in the second picture, I mean with the measure and not the measure only ?

TIA

MattAllington
Posts: 919
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: How to calculate 2 values from the same column

I mean the pivot table, sorry.

Create a new pivot table
Put the 4 columns in your table 2 on rows in the pivot
Change the pivot table settings to be Tabular format and not show sub totals
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

Jam
Posts: 3
Joined: Fri Dec 08, 2017 2:28 am

Re: How to calculate 2 values from the same column

Thank you very much for your help.

I can't get the pivot exactly like I would as the Measure is appearing twice, when I move it to the values.
But this not too important as I was most interested in the calculation.

Thanks again