How to calculate 2 values from the same column

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

How to calculate 2 values from the same column

Postby Jam » Fri Dec 08, 2017 2:36 am

Hi all,

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


and I'd like to transform it like that using PowerPivot:
CapturePivot.PNG
CapturePivot.PNG (6.17 KiB) Viewed 1493 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: 991
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: How to calculate 2 values from the same column

Postby MattAllington » Fri Dec 08, 2017 5:51 am

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 Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

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

Re: How to calculate 2 values from the same column

Postby Jam » Fri Dec 08, 2017 10:28 pm

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: 991
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: How to calculate 2 values from the same column

Postby MattAllington » Sat Dec 09, 2017 7:12 am

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
Add the measure to values
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

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

Re: How to calculate 2 values from the same column

Postby Jam » Mon Dec 11, 2017 7:22 pm

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


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests

cron