Calculate relativity with measure by groups

[PeterChen]

I try to calulate relativity by measure so I can create an interactive plot with filters.

Col1      Col2    Col3    Col4
2014/1/1  A       Y      10
2014/4/1  A       Y      15
2015/1/1  A       Z      15
2015/4/1  A       Z      30
2014/1/1  B       Y      20
2014/4/1  B       Y      30
2015/1/1  B       Z      40
2015/4/1  B       Z      80

The measure I want is relativity, the value in Col4 divide the first value under Col1 and Col2.

Result I supposed but I do not need this in data table since when I create the visualization and add the filter for other columns, Col5, Col6, and etc. that I did not show in this example:

Col1 Col2 Col3 Col4 relativity_Col3ALL relativity_Col3EqualsYorZ

2014/1/1 A Y 10 1 1

2014/4/1 A Y 15 1.5 1.5

2015/1/1 A Z 15 1.5 1

2015/4/1 A Z 30 3 2

2014/1/1 B Y 20 1 1

2014/4/1 B Y 30 1.5 1.5

2015/1/1 B Z 40 2 1

2015/4/1 B Z 80 4 2

I think I need to create another measure for later use.

MinDateCol4 = CALCULATE(SUM(dt[Col4]), FILTER(ALL(dt), dt[Col1] = MIN(dt[Col1]))) 

I think this is incorrect it did not return the value of smallest date by groups.

To calulate relativity, my thought is:

Relativity = CALCULATE(DIVIDE(dt[Col4], MinDateCol4)), ALLSELECTED(dt)) 

Therefore once I plot, the plot will automatically change when I filter other columns.

[v-yuta-msft]

@PeterChen ,

---

@PeterChen wrote:

I try to calulate relativity by measure so I can create an interactive plot with filters.

Col1      Col2    Col3    Col4
2014/1/1  A       Y      10
2014/4/1  A       Y      15
2015/1/1  A       Z      15
2015/4/1  A       Z      30
2014/1/1  B       Y      20
2014/4/1  B       Y      30
2015/1/1  B       Z      40
2015/4/1  B       Z      80

The measure I want is relativity, the value in Col4 divide the first value under Col1 and Col2.

Result I supposed but I do not need this in data table since when I create the visualization and add the filter for other columns, Col5, Col6, and etc. that I did not show in this example:

Col1 Col2 Col3 Col4 relativity_Col3ALL relativity_Col3EqualsYorZ

2014/1/1 A Y 10 1 1

2014/4/1 A Y 15 1.5 1.5

2015/1/1 A Z 15 1.5 1

2015/4/1 A Z 30 3 2

2014/1/1 B Y 20 1 1

2014/4/1 B Y 30 1.5 1.5

2015/1/1 B Z 40 2 1

2015/4/1 B Z 80 4 2

I think I need to create another measure for later use.

MinDateCol4 = CALCULATE(SUM(dt[Col4]), FILTER(ALL(dt), dt[Col1] = MIN(dt[Col1]))) 

I think this is incorrect it did not return the value of smallest date by groups.

To calulate relativity, my thought is:

Relativity = CALCULATE(DIVIDE(dt[Col4], MinDateCol4)), ALLSELECTED(dt)) 

Therefore once I plot, the plot will automatically change when I filter other columns.

---

Could you please clarify what's your requirement and expected result?

Regards,

Jimmy Tao

[PeterChen]

---

@v-yuta-msft wrote:

@PeterChen ,

---

@PeterChen wrote:

I try to calulate relativity by measure so I can create an interactive plot with filters.

Col1      Col2    Col3    Col4
2014/1/1  A       Y      10
2014/4/1  A       Y      15
2015/1/1  A       Z      15
2015/4/1  A       Z      30
2014/1/1  B       Y      20
2014/4/1  B       Y      30
2015/1/1  B       Z      40
2015/4/1  B       Z      80

The measure I want is relativity, the value in Col4 divide the first value under Col1 and Col2.

Result I supposed but I do not need this in data table since when I create the visualization and add the filter for other columns, Col5, Col6, and etc. that I did not show in this example:

Col1 Col2 Col3 Col4 relativity_Col3ALL relativity_Col3EqualsYorZ

2014/1/1 A Y 10 1 1

2014/4/1 A Y 15 1.5 1.5

2015/1/1 A Z 15 1.5 1

2015/4/1 A Z 30 3 2

2014/1/1 B Y 20 1 1

2014/4/1 B Y 30 1.5 1.5

2015/1/1 B Z 40 2 1

2015/4/1 B Z 80 4 2

I think I need to create another measure for later use.

MinDateCol4 = CALCULATE(SUM(dt[Col4]), FILTER(ALL(dt), dt[Col1] = MIN(dt[Col1]))) 

I think this is incorrect it did not return the value of smallest date by groups.

To calulate relativity, my thought is:

Relativity = CALCULATE(DIVIDE(dt[Col4], MinDateCol4)), ALLSELECTED(dt)) 

Therefore once I plot, the plot will automatically change when I filter other columns.

---

Could you please clarify what's your requirement and expected result?

Regards,

Jimmy Tao

---

I want to create measure to calculate the relativity, each value of Col4 / the value of first year quarter by their groups said Col2.

Therefore, if I select all in Col3, the result should be:

Col1       Col2     Col3 Col4    relativity_Col3ALL
2014/1/1      A      Y     10                1 
2014/4/1      A      Y     15               1.5
2015/1/1      A      Z     15               1.5 
2015/4/1      A      Z     30                3 
2014/1/1      B      Y     20                1 
2014/4/1      B      Y     30              1.5 
2015/1/1      B      Z     40                2  
2015/4/1      B      Z     80                4 

If I filter Y or Z in Col3, the result will change to:

Col1       Col2     Col3 Col4    relativity_Col3Filter
2014/1/1      A      Y     10                1 
2014/4/1      A      Y     15               1.5
2015/1/1      A      Z     15                1 
2015/4/1      A      Z     30                2 
2014/1/1      B      Y     20                1 
2014/4/1      B      Y     30              1.5 
2015/1/1      B      Z     40                1  
2015/4/1      B      Z     80                2 

[v-yuta-msft · Accepted solution]

@PeterChen ,

Create a measure using DAX below:

relativity_col3ALL = 
VAR Denominator_Index = CALCULATE(MIN(Table1[Index]), FILTER(ALLEXCEPT(Table1, Table1[Col2]), Table1[Col3] = SELECTEDVALUE(Table1[Col3])))
VAR Denominator = CALCULATE(MAX(Table1[Col4]), FILTER(ALLEXCEPT(Table1, Table1[Col2]), Table1[Index] = Denominator_Index))
VAR Numerator = MAX(Table1[Col4])
return
Numerator / Denominator

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PeterChen]

---

@v-yuta-msft wrote:

@PeterChen ,

Create a measure using DAX below:

relativity_col3ALL = 
VAR Denominator_Index = CALCULATE(MIN(Table1[Index]), FILTER(ALLEXCEPT(Table1, Table1[Col2]), Table1[Col3] = SELECTEDVALUE(Table1[Col3])))
VAR Denominator = CALCULATE(MAX(Table1[Col4]), FILTER(ALLEXCEPT(Table1, Table1[Col2]), Table1[Index] = Denominator_Index))
VAR Numerator = MAX(Table1[Col4])
return
Numerator / Denominator

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

---

what is [Index]?

[Col1]?

Also, in the FILTER(), it seems that the function can only count for 2 argument. How can I passed one more in it, rather than only Col3?

[PeterChen]

what is [Index]?

[Col1]?

Also, in the FILTER(), it seems that the function can only count for 2 argument. How can I passed one more in it, rather than only Col3?