Forum Discussion
Create a dynamic filter for budget selection
ParthSoni1901 OK, so you could approach this like the following. If you post the table of data as text I could test the code out. Otherwise, I'm just guessing something like this should work. Note that I have no idea how you want to return this in terms of the visual, I just used a card visual. Note that in my model, I was using Table4 as my base data table.
First measure (Measure 2) guarantees the sort order such that the highest ROI is listed first.
Measure 2 =
VAR __Budget = 20000
VAR __Count = COUNTROWS('Table4') //Get count of rows in context
VAR __Ordered = CONCATENATEX('Table4',[Campaigns] & "*" & [Analyzed ROI] & "*" & [Cost],"|",[Analyzed ROI],DESC)
//this returns a string like SEM*8.15*10000|Instagram*8.1*10000 that is ordered by ROI
VAR __Table1 =
ADDCOLUMNS(
GENERATESERIES(1,__Count,1),
"__Row",PATHITEM(__Ordered,[Value],TEXT)
)
VAR __Table2 =
ADDCOLUMNS(
__Table1,
"__Campaigns",
VAR __RowPath = SUBSTITUTE([__Row],"*","|")
RETURN
PATHITEM(__RowPath,1,TEXT),
"__Analyzed ROI",
VAR __RowPath = SUBSTITUTE([__Row],"*","|")
RETURN
PATHITEM(__RowPath,2,TEXT) + 0,
"__Cost",
VAR __RowPath = SUBSTITUTE([__Row],"*","|")
RETURN
PATHITEM(__RowPath,3,TEXT) + 0
)
VAR __Table3 =
ADDCOLUMNS(
__Table2,
"__RT",
VAR __ROI = [__Analyzed ROI]
RETURN
__Budget - SUMX(FILTER(__Table2,[__Analyzed ROI] >= __ROI),[__Cost])
)
VAR __FinalTable = FILTER(__Table3, [__RT] >= 0)
RETURN
CONCATENATEX(__FinalTable,[__Campaigns] & "|" & [__Analyzed ROI] & "|" & [__Cost] & "|" & [__RT],UNICHAR(10))
This version does not guarantee sort order but is simpler:
Measure 3 =
VAR __Budget = 20000
VAR __Table =
ADDCOLUMNS(
'Table4',
"__RT",
VAR __ROI = [Analyzed ROI]
RETURN
__Budget - SUMX(FILTER('Table4',[Analyzed ROI] >= __ROI),[Cost])
)
VAR __FinalTable = FILTER(__Table, [__RT] >= 0)
RETURN
CONCATENATEX(__FinalTable,[Campaigns] & "|" & [Analyzed ROI] & "|" & [Cost] & "|" & [__RT],UNICHAR(10))
The first measure converts the table to as string ordered by ROI. This is then converted back to a table by using a FOR loop (__Table1) and then parsing out the string text (__Table2). This guarantees sort order. Then, a WHILE loop (__Table3) to figure out when you have used up your budget. The (__FinalTable) "exits" the WHILE loop.
Measure 3 just uses the WHILE loop.
Sorry, But this is not what I wanted was clustred column chart where cost is distributed based on ROI and we can select based on campaigns and weeks. budget has to be distributed between the selected weeks and campaigns.
I am attaching my Data in here.
| SEM | 8.15 | 10000 | 2 | 10000 |
| 8.1 | 10000 | 2 | 10000 | |
| PPC | 8.05 | 10000 | 2 | 10000 |
| Youtube | 8.02 | 10000 | 2 | 10000 |
| 8 | 10000 | 2 | 10000 | |
| 7.95 | 10000 | 2 | 10000 | |
| SEM | 7.92 | 20000 | 2 | 10000 |
| 7.9 | 20000 | 2 | 10000 | |
| Youtube | 7.8 | 20000 | 2 | 10000 |
| 7.75 | 20000 | 2 | 10000 | |
| PPC | 7.72 | 20000 | 2 | 10000 |
| 7.7 | 20000 | 2 | 10000 | |
| Youtube | 7.68 | 30000 | 2 | 10000 |
| 7.65 | 30000 | 2 | 10000 | |
| SEM | 7.62 | 30000 | 2 | 10000 |
| 7.6 | 30000 | 2 | 10000 | |
| SEM | 7.55 | 10000 | 1 | 10000 |
| 7.5 | 10000 | 1 | 10000 | |
| PPC | 7.45 | 10000 | 1 | 10000 |
| SEM | 7.43 | 40000 | 2 | 10000 |
| Youtube | 7.42 | 10000 | 1 | 10000 |
| 7.4 | 10000 | 1 | 10000 | |
| 7.39 | 40000 | 2 | 10000 | |
| Youtube | 7.37 | 40000 | 2 | 10000 |
| SEM | 7.35 | 10000 | 3 | 10000 |
| 7.34 | 40000 | 2 | 10000 | |
| 7.33 | 10000 | 1 | 10000 | |
| SEM | 7.32 | 20000 | 1 | 10000 |
| 7.3 | 20000 | 1 | 10000 |