Forum Discussion
Normalizing Data based on attribute AND selected "Product" selected from a slicer.
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 |
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)
2 Replies
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?
