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

Min - 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.

Screenshot 2021-09-13 175130.jpg

 

 
Good Measures:

T0_Min_Value =
CALCULATE(SUM(ie_buffer_combined[VALUE]), ie_buffer_combined[FolderID1] = "T0")

Tn_Min_Value =
CALCULATE(SUM(ie_buffer_combined[VALUE]), ie_buffer_combined[FolderID1] = "Tn")


Bad Measure

Delta T0-Tn =
T0_Min_Value - Tn_Min_Value


 How would I write the third measure using the first two as reference values?

Thank you.

10 REPLIES 10
roncruiser
Helper V
Helper V

@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.

  1. 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.

Hi,

I just do not understand your requirement.  Sorry but i cannot help.


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

@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])
    )
)

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

@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 SN2Incorrect Delta for SN1 and SN2 Screenshot 2021-09-16 0744011.jpg

 


Lowest Level of the data the difference is correct.

Lowest LevelLowest Level

 
One level up, the difference values fall apart.

One Level UpOne 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


@roncruiser I am fairly certain that you are going to need to modify your calculation at different levels in the hierarchy. You are using MAX([Value]) essentially. So, at the lowest level of data it is providing the correct results and at hierarchies above that it is also providing correct results. For example for SN1, 0, 0, 0 you have 9. This is indeed correct. That is the highest value for all detail rows. So, the calculation is correct but it is not what you want. You will therefore need to create a measure that figures out where you are in the hierarchy and performs the correct calculation. You need MM3TR&R: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition
roncruiser
Helper V
Helper V

@Greg_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_Mathur 

Ashish, I'll post it up soon.  Hopefully by the end of today.

Thanks.... working on it....

Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file.


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

@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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

@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.
Screenshot 2021-09-13 175130.jpg













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.

@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.

Greg_Deckler_0-1631588592922.png

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

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors