Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
@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 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.
@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])
)
)
@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".
Lowest Level of the data the difference is correct.
One level up, the difference values fall apart.
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
@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, I'll post it up soon. Hopefully by the end of today.
Thanks.... working on it....
Hi,
Share the link from where i can download your PBI file.
@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.
@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:
@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 |
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |