How to apply Filter in Data Explorer Formula

  • Article

I ran into below particular scenario.

My Sample Table:

image

 

Desired Output:

image

 

In PowerPivot DAX Formula, I could easily achieve this as this -

image

Actual Formula:

=[Market Value]/SUMX(FILTER(mySampleTable,mySampleTable[PlanName]="ABC"),mySampleTable[Market Value])

 

How can I achieve this in Data Explorer?  Let’s see -

Step1: Open Data Explorer, select table and choose “From Table” option

image

 

Step 2:

Filter for value “ABC”

image

 

Step 3:

Copy the formula from the formula bar and paste in a notepad (we will use it later)

image

 

Step 4:

Remove filter and add a new Custom Column

image

 

Step 5: MAGIC – here is the main thing – write a formula in DE!

Insert [Market Value] and then rest of the formula as below (use the copy/paste from previously copied formula for filtering PlanName = “ABC”

image

 

You should something like this – Now the new added custom column is showing the % of ABC.

image

At this point you can accept the query and add into your model – DONE!

image