0

I have a data that I simply create: 

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

I want to create a measure in Power BI so I can create an interactive visualization. The above data is created for example so we need to suppose that col2, col3 have multiple factors.

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

So I plot it and add filters beside the plot. When I select Y in the filter Col3, the plot will automatically change.

I provide the code I think in R: 

dt <- data.table::as.data.table(dt)
dt[, relativity := Col4 / Col4[1], by = .(Col1, Col2)]

But above code is incorrect because it did not consider Col3. I just want to mention Col4 / Col4[1] or Col4 / first(Col4).

I tried measure in Power BI: 

relativity = CALCULATE(DIVIDE(dt[Col4], dt[AnotherMeasure]), MIN(dt[Col1]))

I know this is false.
Can anyone help?

UPDATE
I tried @Alexis Olson's code and modified as: 

relativity = 
VAR YR = MIN(dt[Col1].[Year])
VAR QT = MIN(dt[Col1].[Quarter])
VAR PF = CALCULATE(TOTALQTD(SUM(dt[Col4]), dt[Col1].[Date]), dt[Col1].[Year] = YR, dt[Col1].[Quarter] = QT) 
RETURN
    DIVIDE(SUM(dt[Col4]), PF)

However, when I visualize in the report, it all shows 1.

I also tried this: 

relativity = 
VAR YR = CALCULATE(MIN(dt[Col1].[Year]), ALLEXCEPT(dt, dt[Col2]))
VAR QT = CALCULATE(MIN(dt[Col1].[Quarter]), ALLEXCEPT(dt, dt[Col2]))
VAR PFQTD = TOTALQTD(SUM(dt[Col4]), dt[Col1].[Date])
VAR MPFQTD = CALCULATE(MAX(PFQTD), FILTER(dt, dt[Col1].[Year] = YR), FILTER(dt, dt[Col1].[Quarter] = QT)) 
RETURN
    MPFQTD

Failed either

Peter Chen
  • 1,464
  • 3
  • 21
  • 48
  • you are providing only `Col4`, then how will it consider `Col3` – akrun May 28 '19 at 19:06
  • The hard part here is doing the lookup for what to divide by, but this may help you with that: https://stackoverflow.com/questions/52525377/return-top-value-ordered-by-another-column – Alexis Olson May 28 '19 at 19:07
  • I mean if there are two factors `Y` and `Z` under `Col3`, relativity will change when I filter them. – Peter Chen May 28 '19 at 19:07
  • @PeterChen `the value in Col4 divide the first value under Col1 and Col2.`, how do you want to change for 'Col3' If it is another factor, place it in `by = .(Col1, Col2, Col3)` – akrun May 28 '19 at 19:08
  • @akrun If I just want to create a table that group by `Col1~Col3`, I can simply do with `by`. However, if the relativity changes once I filter different factors in `Col3`, that way is incorrect – Peter Chen May 28 '19 at 19:11
  • @akrun I edited my question – Peter Chen May 28 '19 at 19:15
  • Do you need a function so that subset by the values of 'Col3' and then create the column. like in shiny UI – akrun May 28 '19 at 19:29
  • @akrun I thought it can be done with `measure` in `Power BI`. As @Alexis Olson mentioned, I think the way is to create another measure to lookup the value of `Col4` in `min(Col1)` first. – Peter Chen May 28 '19 at 19:31
  • @AlexisOlson I tried to create another measure first, `MinDate_Col4 = CALCULATE(SUM(dt[Col4]), FILTER(ALL(dt), MIN(dt[Col1])))`. It seems incorrect. Do you think I can calculate this based on other columns? – Peter Chen May 28 '19 at 19:46
  • Do you want `relativity` to be based on the `Col3` value in that row or over all selected `Col3` values? – Alexis Olson May 29 '19 at 15:45
  • I want over all selected Col3. So I can use dynamic table in report. Also, I need `measure` so I can manipulate date hierarchy in report tab and it changes the value automatically. – Peter Chen May 29 '19 at 15:51

1 Answers1

0

Using the logic from this Q&A, you can create a calculated column as follows:

relativity =
VAR FirstCol1 = CALCULATE ( MIN ( dt[Col1] ), ALLEXCEPT ( dt, dt[Col2], dt[Col3] ) )
VAR FirstCol4 = CALCULATE ( VALUES ( dt[Col4] ), dt[Col1] = FirstCol1 )
RETURN
    DIVIDE ( dt[Col4], FirstCol4 )

This looks up the first date when Col2 and Col3 are the same value, then finds the Col4 value on that first date, and finally divides the current Col4 value by that first Col4 value.

The ALLEXCEPT removes the row context except for the columns you specify. If you want relativity_Col3_All, then simply remove that column from the ALLEXCEPT specification.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Could you please explain more on `ALLEXCEPT`? – Peter Chen May 29 '19 at 11:59
  • Moreover, is it possible to calculate `FirstCol1` when I visualize the in the report. To clarify, it means that if I create a Table in report, the relativity will automatically change based on what I choose to put in this table, also the date hierarchy. Thank you. – Peter Chen May 29 '19 at 12:13
  • I searched for the `ALLEXCEPT`, but still get a little bit confused. It seems that when I add `ALLEXCEPT`, I get `relativity_Col3EqualsYorZ`. However, this will not automatically change to get the value of `relativity_Col3_All` once I select all in the report tab and create a table. – Peter Chen May 29 '19 at 12:23
  • 1
    If you are looking for something dynamic, then it cannot be a calculated column, but rather a measure, which needs to be written differently. – Alexis Olson May 29 '19 at 13:48
  • yes. that is what I want to create for dynamic with measure. Does the answer above fit with the measure? I did not get the correct result in the report tab. – Peter Chen May 29 '19 at 13:52