Skip to main content
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

Helper V
Helper V

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



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 =
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:
show count values.jpg
 show count.jpg
Difference when the values are summed.  This is Correct!!!
show sum.jpg
Values when showing the Maximum:
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.
Helper V
Helper V

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.

Community Champion
Community Champion

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.


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


Community Champion
Community Champion

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?



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.





Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors