Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello to the team,
I have been trying to do some min max normalization on average value, I am sorry because I have found some resources here but I can't seem to make it work. It's my first time using powerBI but I have some code experience.
My original data is on this format:
Name | Date | Attribute | Value_1 | Value_2 |
John | 2010-01-10 | Speed | 50 | 30 |
John | 2010-01-19 | Speed | 70 | 50 |
John | 2010-01-10 | Stamina | 30 | 40 |
John | 2010-01-19 | Stamina | 65 | 40 |
Karl | 2010-01-10 | Speed | 50 | 20 |
Karl | 2010-01-19 | Speed | 70 | 60 |
Karl | 2010-01-10 | Stamina | 20 | 50 |
Karl | 2010-01-19 | Stamina | 30 | 15 |
Anna | 2010-01-10 | Speed | 60 | 25 |
Anna | 2010-01-19 | Speed | 20 | 65 |
Anna | 2010-01-10 | Stamina | 30 | 55 |
Anna | 2010-01-19 | Stamina | 60 | 15 |
So as you can see I have several names, and several attributes, for all of those attributes and names I have two key values that have been collected through time, goal is to study those two values independantly to plot them afterwards
What I want to do, is to make a min-max normalization of the average of those attributes per name, and have all of this dynamically change when I reduce the time frame.
If we decompose it, it looks like this:
it's for Value 1 and Value 2 independantly so let's take value_1 for instance:
- Calculate the average value for each attribute, per name.
- Found the max and min of those average for each attribue
- Normalize the averages previously calculated.
Could you help me please ?
Thank you very much
Useful output would be something like this
| Value | John | Karl | Anna |
Speed | Value_1 | 0 | 1 | 0.6 |
Speed | Value_2 | 0.6 | 1 | 0 |
Stamina | Value_1 | 0.4 | 0 | 1 |
Stamina | Value_2 | 1 | 0.9 | 0 |
Solved! Go to Solution.
Hi @Anonymous ,
Use the following measures:
averagE1 =
VAR averagE1 =
CALCULATE (
ROUND ( AVERAGE ( Table1[Value] ), 1 ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Attribute] = MAX ( 'Table1'[Attribute] )
&& Table1[Name] = MAX ( Table1[Name] )
&& Table1[vlauetype] = MAX ( Table1[vlauetype] )
)
)
RETURN
averagE1
maxvalue2 =
CALCULATE (
MAXX ( Table1, [averagE1] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Attribute] = MAX ( 'Table1'[Attribute] )
&& Table1[vlauetype] = MAX ( Table1[vlauetype] )
)
)
minvalue2 =
CALCULATE (
MINX ( Table1, [averagE1] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Attribute] = MAX ( 'Table1'[Attribute] )
&& Table1[vlauetype] = MAX ( Table1[vlauetype] )
)
)
Out2 =
ROUND ( ( [averagE1] - [minvalue2] ) / ( [maxvalue2] - [minvalue2] ), 2 )
Final you will get what you want!
Wish it could help you!
Best Regards
Lucien
Hi @Anonymous ,
Use the following measures:
averagE1 =
VAR averagE1 =
CALCULATE (
ROUND ( AVERAGE ( Table1[Value] ), 1 ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Attribute] = MAX ( 'Table1'[Attribute] )
&& Table1[Name] = MAX ( Table1[Name] )
&& Table1[vlauetype] = MAX ( Table1[vlauetype] )
)
)
RETURN
averagE1
maxvalue2 =
CALCULATE (
MAXX ( Table1, [averagE1] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Attribute] = MAX ( 'Table1'[Attribute] )
&& Table1[vlauetype] = MAX ( Table1[vlauetype] )
)
)
minvalue2 =
CALCULATE (
MINX ( Table1, [averagE1] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Attribute] = MAX ( 'Table1'[Attribute] )
&& Table1[vlauetype] = MAX ( Table1[vlauetype] )
)
)
Out2 =
ROUND ( ( [averagE1] - [minvalue2] ) / ( [maxvalue2] - [minvalue2] ), 2 )
Final you will get what you want!
Wish it could help you!
Best Regards
Lucien
Thank you very much that's perfect it works, and I learnt a lot on how PowerBI handles variables.
It's very slow so I might need to look into that later, but this is an another story. Thank you !
Hi @Anonymous ,
OK,I see. Try like below steps:
Step1,unpivot the base table:
Step 2,calculate the average:
average = CALCULATE(ROUND(AVERAGE(Table1[Value]),1),ALLEXCEPT(Table1,Table1[Attribute],Table1[Name],Table1[vlauetype]))
Step 3,accord the average value ,new column to get the max value ,and the min value:
maxvalue = CALCULATE(MAX(Table1[average]),ALLEXCEPT(Table1,Table1[Attribute],Table1[vlauetype]))
minvalue = CALCULATE(min(Table1[average]),ALLEXCEPT(Table1,Table1[Attribute],Table1[vlauetype]))
Final ,accord the column to get out put:
out = round((Table1[average]-Table1[minvalue])/(Table1[maxvalue]-Table1[minvalue]),2)
Final create the visualization:
You could download my pbix file if you need!
Wish it is helpful for you!
Best Regards
Lucien
Thank you very much !
I successfully got what I wanted, didn't think I could use customed columns.
I have one additional question, how can I make the table dynamically change with a date slicer ? Maybe slicers can't be used in calculated column, and I should therefore try to convert what you suggested into measures ?
Thank you !
Hi @Anonymous ,
Not very clearly about how to get the output.
- Calculate the average value for each attribute, per name.
Just like John ,Speed , is the value is :
(John 2010/1/10 speed value1+John 2010/1/10 speed value2+ John 2010/1/19 speed valu1e+ John 2010/1/19 speed value2)/4?
And how to get the final result that John speed value1=1 and Karl speed value=1?
Best Regards
Lucien
Hi @v-luwang-msft ,
So sorry it wasn't clear. The normalization has to be done on value 1 and value 2 independantly, so let's take value 1 for instance.
First we calculate the average of Value 1 for each of the attribute and for each name.
Table 1:
John | Karl | Anna | |
Speed | 60 | 60 | 40 |
Stamina | 47.5 | 25 | 45 |
Then we need to find the max and min of the averaged values for the normalization
Table 2:
Min | Max | |
Speed | 40 | 60 |
Stamina | 25 | 47.5 |
Now that we have those values we can normalize our averages from table 1.
So we do :
(value-min)/(max-min)
Table 3:
John | Karl | Anna | |
Speed | 1 | 1 | 0 |
Stamina | 1 | 0 | 0.89 |
We then do the same thing for value 2 which gives use another set of normalized values.
I hope it's clearer, thank you.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |