How to apply Filter in Data Explorer Formula
I ran into below particular scenario.
My Sample Table:
Desired Output:
In PowerPivot DAX Formula, I could easily achieve this as this -
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
Step 2:
Filter for value “ABC”
Step 3:
Copy the formula from the formula bar and paste in a notepad (we will use it later)
Step 4:
Remove filter and add a new Custom Column
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”
You should something like this – Now the new added custom column is showing the % of ABC.
At this point you can accept the query and add into your model – DONE!