Forum Discussion
Post PatronMin - Min Values not Showing Up correctly
Not sure What I'm doing wrong. Can someone help.
For each device I wrote a measure to find the min at T0 and Tn. Those two measures work fine.
The Values for the Difference Totals are incorrect. Which are the values I highlighted in yellow. These are all incorrect.
I simply want to find the difference between the Mins no matter what the data level, but the way I wrote the measure it gives the min calculated difference for each device. There are multiple levels to this data.
It's when it's at the Lowest Levele of Data the difference values are correct.
Expected are the green values.
Good Measures:
Bad Measure
How would I write the third measure using the first two as reference values?
Thank you.
10 Replies
roncruiserGreg_Deckler
Greg, my data is definitely different. I'm working on a sample set of data somewhat replicating the real data and what is expected.
Just need a little more time to hack it all together. Then I can share what's happening properly.Ashish, I'll post it up soon. Hopefully by the end of today.
Thanks.... working on it....- Greg_Deckler
Community Champion
roncruiser Are those really SUM's or are they MIN's? Would need to understand the underlying data better. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.- roncruiser
Greg_Deckler
I was trying the meaures every which way get it to work in desperation. As the Measure names show, they started off as MIN's, and ended up as SUM's with my last stab at it trying to get it to work. It's suppose to be MIN's ideally.
Expected is the difference of Column1 minus Column 2 per device.
The data set is massive. I tried to simplify but what I'm trying achieve is more complex than I can articulate and share.
Column 1 - Column 2 = Column 3
Green Values are expected.
Column 1:T0_Min_Value =CALCULATE(SUM(ie_buffer_combined[VALUE]), ie_buffer_combined[FolderID1] = "T0")
Column 2:Tn_Min_Value =CALCULATE(SUM(ie_buffer_combined[VALUE]), ie_buffer_combined[FolderID1] = "Tn")Delta T0-Tn =T0_Min_Value - Tn_Min_Value
Each column will be plotted as separate lines on a line chart, and each will be shown in the legend of the line chart. Then with a Slicer choose which of the three lines I want to show. The deltas for thousands of devices will calculated.I'll see about simplifying some data to post or share. Hope this is good enough for now. Just need some ideas.- Greg_Deckler
roncruiser Hmm, well, I created a table like below and was able to get the correct answers so obviously something is different about your data.
DeviceStateAttributeValue
00294B9E 0 T0 120 00294B9E 0 Tn 108 00294B9E 0 T0 140 00294B9E 0 Tn 140 00294B9E 1 T0 120 00294B9E 1 Tn 105 00294B9E 1 T0 140 00294B9E 1 Tn 140 00294B9E 2 T0 126 00294B9E 2 Tn 105 00294B9E 2 T0 140 00294B9E 2 Tn 140 00294B9E 3 T0 123 00294B9E 3 Tn 102 00294B9E 3 T0 140 00294B9E 3 Tn 140 091BA07B 0 T0 120 091BA07B 0 Tn 102 091BA07B 0 T0 150 091BA07B 0 Tn 150 091BA07B 1 T0 120 091BA07B 1 Tn 105 091BA07B 1 T0 150 091BA07B 1 Tn 150 091BA07B 2 T0 117 091BA07B 2 Tn 99 091BA07B 2 T0 150 091BA07B 2 Tn 150 091BA07B 3 T0 120 091BA07B 3 Tn 96 091BA07B 3 T0 150 091BA07B 3 Tn 150
- Ashish_Mathur
Super User
Hi,
Share the link from where i can download your PBI file.
- roncruiser
Greg_Deckler
Ashish_Mathur
Okay. I have something. Finally. Sorry it took so long.
https://drive.google.com/file/d/1b_-KSEway-TdS0Zrb7a1rTy6J0X7n2Wo/view?usp=sharing
There are two tabs in the .pbix.Tab 1- Original
Tab 2- Expected: Clickable Legend.
The Expected: Clickable Legend table was derived from the Original table.In the Expected: Clickable Legend tab, there are two expectations that are not happening.
- The difference values are not calculating properly in both the table and line chart.
- T0-Tn1
- T0-Tn2
The expressions for the deltas are from the measures written in the orginal table.
The foundation of this exercise is to have the legend clickable. So, I can choose what to show from the "Clickable Legend" slicer. I can show one or two or all parameters. Just by clicking the Slicer.
I just need to get the pesky difference calculation to show the values correctly no matter the level of the data I'm at.
Will mostly be at the device level.
The line chart is key. Getting the deltas to show properly there is all that needs to be fixed.
Thanks for your patience. Hope it's easy. It wasn't for me.- Greg_Deckler
roncruiser Not going to lie, that's kind of a mess. I can't really be sure what you actually want. For example, for this line, what *should* the value be and why?
Device C R By Bt Clickable legend T0 Tn1 Tn2 value SN2 2 1 1 4 T0-Tn1 120 111 102 9 What should "value" be if not 9?
Try this:
Expected_Table = UNION ( SUMMARIZE( Original, Original[Device],Original[C],Original[R],Original[By],Original[Bt], "Clickable Legend", "Tn1", "T0", [T0], "Tn1", [Tn1], "Tn2", [Tn2], "value", [Tn1] ), SUMMARIZE( Original, Original[Device],Original[C],Original[R],Original[By],Original[Bt], "Clickable Legend", "Tn2", "T0", [T0], "Tn1", [Tn1], "Tn2", [Tn2], "value", [Tn2] ), SUMMARIZE( Original, Original[Device],Original[C],Original[R],Original[By],Original[Bt], "Clickable Legend", "T0", "T0", [T0], "Tn1", [Tn1], "Tn2", [Tn2], "value", [T0] ), SUMMARIZE( Original, Original[Device],Original[C],Original[R],Original[By],Original[Bt], "Clickable Legend", "T0-Tn1", "T0", [T0], "Tn1", [Tn1], "Tn2", [Tn2], "value", MINX(FILTER(Original,[Tn] = "T0"),[Value]) - MINX(FILTER(Original,[Tn]="Tn1"),[Value]) ), SUMMARIZE( Original, Original[Device],Original[C],Original[R],Original[By],Original[Bt], "Clickable Legend", "T0-Tn2", "T0", [T0], "Tn1", [Tn1], "Tn2", [Tn2], "value", MINX(FILTER(Original,[Tn] = "T0"),[Value]) - MINX(FILTER(Original,[Tn]="Tn2"),[Value]) ) )- roncruiser
Greg_Deckler
It should be 9 at the device data level for the example you provided.
I've tried your example and I'm still getting weird incorrect difference values. This is stumping me.
Screenshot is from Tab 2 "Expected: Clickable Legend".Incorrect Delta for SN1 and SN2
Lowest Level of the data the difference is correct.Lowest Level
One level up, the difference values fall apart.One Level Up
The difference is incorrect the further I push into or out of the data. The only time it's correct is when I'm at the lowest level of data. I'm trying to understand why. Maybe this cannot be done in a separate table using Summarize, or maybe it's my side., or maybe it's me.
I've reloaded: With different rows of data shown in the 2nd screenshot. Tab 2 the Clickable Legend is the pertinent tab.
https://drive.google.com/file/d/1eGmai4tyl3y29SUq3eyfErTrCRKtGVt9/view?usp=sharing
- Ashish_Mathur
Hi,
I just do not understand your requirement. Sorry but i cannot help.
- The difference values are not calculating properly in both the table and line chart.
