Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have the below data. I have unpivoted the data to have the columns Product, Attribute, Value. Using the following
Normalized Value = VAR MinOfGroup = CALCULATE(MIN('Table'[Value]),ALLEXCEPT('Table','Table'[Attribute])) VAR MaxOfGroup = CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Attribute])) VAR DetailValue = MAX('Table'[Value]) RETURN DIVIDE(DetailValue - MinOfGroup,MaxOfGroup - MinOfGroup,0)
from https://dataveld.com/2017/07/29/using-dax-to-normalize-data-in-power-bi/
we can get the normalize values for each Attribute. However, I really need to be able to filter the data by "Product" using a slicer. This works but the calculation always using all products to normalize the data between 0 and 1. How can we get the data normalized always between 0-1 on our bar chart? As you see below, the normalized value doesn't go between 0 and 1. Many thanks to all.
Product | Speed | Torque | Power | Flow | Sound |
Big Hoopa | 15000 | 38 | 570 | 200 | 90 |
Tim's Dream | 16000 | 55 | 880 | 600 | 80 |
First Fury | 17000 | 66 | 1122 | 300 | 78 |
Last Time Out | 11000 | 34 | 374 | 175 | 87 |
Musty Mitten | 10000 | 11 | 110 | 325 | 95 |
Compact Tech | 13500 | 110 | 1485 | 800 | 91 |
NextGen | 12000 | 80 | 960 | 300 | 77 |
Tough Tool | 9000 | 45 | 405 | 375 | 78 |
Solved! Go to Solution.
@LeeDubs , Try like
Normalized Value =
VAR MinOfGroup = CALCULATE(MIN('Table'[Value]),filter(allselected('Table'),'Table'[Attribute] = max('Table'[Attribute])))
VAR MaxOfGroup = CALCULATE(MAX('Table'[Value]),filter(allselected('Table'),'Table'[Attribute] = max('Table'[Attribute])))
VAR DetailValue = MAX('Table'[Value])
RETURN DIVIDE(DetailValue - MinOfGroup,MaxOfGroup - MinOfGroup,0)
@LeeDubs , Try like
Normalized Value =
VAR MinOfGroup = CALCULATE(MIN('Table'[Value]),filter(allselected('Table'),'Table'[Attribute] = max('Table'[Attribute])))
VAR MaxOfGroup = CALCULATE(MAX('Table'[Value]),filter(allselected('Table'),'Table'[Attribute] = max('Table'[Attribute])))
VAR DetailValue = MAX('Table'[Value])
RETURN DIVIDE(DetailValue - MinOfGroup,MaxOfGroup - MinOfGroup,0)
Wow. Thank you so much. This worked. I have struggled and would have never gotten this. Can you please breakdown and explain what the code in MinOfGroup (and MaxOfGroup) is doing?