cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
roncruiser
Helper IV
Helper IV

Pivoting Measures into a new Table results in Two Values per Measure at lowest Level of the Data

Hello,

 

I pivoted 3 measures into a new table.  The measures simply subtract two values derived from Calculate: A-B.

In the original table the difference is shown as a single value with a count of one.

 

The new table has a Measure column and Value column, but the value from measures come in as two values at it's lowest level of data.  The two values need to be summed to show the difference.  I was expecting a single value which is the difference.  

The difference is the correct value at the lowest level of data, but when the data is collapsed the values sum and no longer show the difference.

 

How do I bring in the difference as a single value rather than two values that require a sum?

Measures in original table.

 

 

Measure1 = CALCULATE(MIN(Table[VALUE]), ie_buffer_combined[FolderID1] = "T" ) - CALCULATE(MIN(Table[VALUE]), ie_buffer_combined[FolderID1] = "1" )
Measure2 =  
CALCULATE(MIN(Table[VALUE]), ie_buffer_combined[FolderID1] = "T" ) - CALCULATE(MIN(Table[VALUE]), ie_buffer_combined[FolderID1] = "2" )
Measure3 =
CALCULATE(MIN(Table[VALUE]), ie_buffer_combined[FolderID1] = "T" ) - CALCULATE(MIN(Table[VALUE]), ie_buffer_combined[FolderID1] = "3" )

Measure to create New Table:
New Table =
UNION (
SUMMARIZE ( Table, "Measure", "1", "T", [T], "value", [1] ),
SUMMARIZE ( Table, "Measure", "2", "T", [T], "value", [2] ),
SUMMARIZE ( Table,"Measure", "3", "T", [T], "value", [3] )
)
 
Actual example of what I'm seeing in the new table:
Count
show count values.jpg
 show count.jpg
 
Difference when the values are summed.  This is Correct!!!
show_sum.jpg
show sum.jpg
 
Values when showing the Maximum:
show_max_value.jpg
show max.jpg
 
Values when showing the Minimum:
show min values.jpg
show min.jpg
 
Values when the data is collapsed.  This is incorrect!  The rows are being summed.  The difference should seen.
collapsed values.jpg
 
The data shows correctly in the orginal table.  The new table is the problem.
 
Hoping someone can help.
5 REPLIES 5
roncruiser
Helper IV
Helper IV

Thanks for your help...

Just some more information to help with clarification.
All the new table scatter plot needs to show is the largest (MAXX) delta at any level in the data hierarchy.


It's not doing that and I'm now I am unsure I am doing things correctly.

littlemojopuppy
Super User
Super User

That would be helpful...thank you!

 

Is it far to assume that your scatter plot is Measure A vs. Measure B?  Or Measure A vs Measure C, etc.

@littlemojopuppy 

https://drive.google.com/file/d/1b_-KSEway-TdS0Zrb7a1rTy6J0X7n2Wo/view?usp=sharing

Here's an accurate but simplified form of the data and scatter plots involved.
Original vs New Table
Orginal Scatter and Table on the left side,

and New Scatter and Table on the right side.

 

I want to plot the differences at any level of the data, but the differences are not maintained as the data is collapsed.

 

The New Table scatter plot should show deltas per Measure along the X-axis:

 

Y-axis = T0

 

X-axis = Maxx Delta T0-T1, Maxx Delta T0-T2, Maxx Delta T0-T3

 

littlemojopuppy
Super User
Super User

Hi!  Without explaining the mechanics of what you did, can you explain what you're trying to accomplish?  Why did you pivot three measures into a new table?  Because I'm willing to bet there's an easier way to do this.

And can you provide sample data and definition of the measures in question?

@littlemojopuppy 
Sure.

 

The main reason for the new table is to show all the measures as legend in a Scatter Chart,
and show the results of the measures along the X-axis of the Scatter Chart.

Then I can view all the data along the X-axis as plotted against T in the Y-axis.
Each measure showing as a three distinct groupings in the scatter plot,


In the original table, each measure will show as it's own column.  With only a single measure

getting plotted along the x-axis and showing in the legend at one time.  Not all measure values can be plotted along the X-axis of the scatter chart in the original table.  (I don't know how to get three separate measure into the X-axis and showing in the legend of the scatter chart).

 

I'll provide a sample set of data.

 

-Corrected.

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.