Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
P_P2595
Helper I
Helper I

Calculating variance from multiple columns from the same table

Hi Team,

I have the below input and want to calculate variance based on the description column. also want the output to be in table or matrix form.

Input:

iddescriptionsymptomsconduct_problemhyperactivityproblemprosocialimpactscore
150 Start471086415
150End3584777
160Start735741018
160Start55562816
160End55562613
160End54492818
164 Start722561019
164End54258714
173 Start971074410
173End8797298
188 Start7370969
188End73506516
189 Start681057412
189End46947211

Expected output:

ScaleStartEnddiff
symptoms6.435.291.14
conduct_problem5.004.860.14
hyperactivity7.006.001.00
problem5.435.000.43
prosocial5.434.860.57
impact6.576.290.29
score14.1412.431.71
all Total50.0044.715.29

 

Please not that the value of Start and End is the average value based on id column. and the all Total is the summantion of the 7 rows.

Is this possible in power bi?

 

Thanks in advance.

3 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@P_P2595 , Unpivot the all column other than Id and description , then you can have measures like

 

M1 = calculate(Average(Table[Value]), filter(Table, Table[Description] ="Start") )

 

M2= calculate(Average(Table[Value]), filter(Table, Table[Description] ="End") )

 

Diff = [M1] -[M2]

 

Learn Power BI: Power Query UnPivot Data, Pivot Data, Transpose Data, Query Fill Up, Fill Down: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=36407s

 

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

View solution in original post

Ahmedx
Super User
Super User

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RiGy6Yf6quAn_ATjG?e=zsB0R6

Screen Capture #1253.pngScreen Capture #1254.pngScreen Capture #1257.png

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

Please find attached my solution file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
P_P2595
Helper I
Helper I

Hi all,
Thanks for your solutions and it’s working for me. Is it possible to get the same output without unpivoting the columns as I have some pages and visuals with that table and extra columns than the input data and I don't want to unpivot the data because it's affecting other pages and visuals.
Is there any other way to get the expected output by creating a measure and calculated column?
Thank you so much.
Greatly appreciate your answers.

Ashish_Mathur
Super User
Super User

Hi,

Please find attached my solution file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ahmedx
Super User
Super User

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RiGy6Yf6quAn_ATjG?e=zsB0R6

Screen Capture #1253.pngScreen Capture #1254.pngScreen Capture #1257.png

amitchandak
Super User
Super User

@P_P2595 , Unpivot the all column other than Id and description , then you can have measures like

 

M1 = calculate(Average(Table[Value]), filter(Table, Table[Description] ="Start") )

 

M2= calculate(Average(Table[Value]), filter(Table, Table[Description] ="End") )

 

Diff = [M1] -[M2]

 

Learn Power BI: Power Query UnPivot Data, Pivot Data, Transpose Data, Query Fill Up, Fill Down: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=36407s

 

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.