Convert Tableau calculated filed into Power BI

[pm_97]

Hi,

I am in the process of converting a load of Tableau dashboards into Power BI but cannot figure out how to translate this particular measure and was hoping someone could point me in the right direction:

Measure from Tableau:

((RUNNING_SUM(SUM(IF  [age]=9 OR [age]=10  THEN [no_of_records] END))-
IFNULL(RUNNING_SUM(SUM(IF  [age]=0 OR [age]=1 OR [age]=2 OR [age]=3 OR [age]=4 OR [age]=5 OR [age]=6 THEN [no_of_records] END)),0))/ 
RUNNING_SUM(SUM([no_of_records]))*100)

age is an int field, contains values from 0 - 10.  no_of_records also  an int field

Tried converting this into Power BI as:

test =

Var Result1=

 CALCULATE(CALCULATE( SUM(Query1[no_of_records]),FILTER(Query1,(Query1[age]) = 9 || Query1[age] = 10)), FILTER(  Query1, Query1[Start of Week] <= MAX(Query1[Start of Week]))) 

Var Result2=

 CALCULATE(CALCULATE( SUM(Query1[no_of_records]),FILTER(Query1,(Query1[age]) = 0 || Query1[age]) = 1 || Query1[age]) = 2 || Query1[age]) = 3 || Query1[age]) = 4 || Query1[age]) = 5 || Query1[age]) = 6)),

 FILTER(  Query1, Query1[Start of Week] <= MAX(Query1[Start of Week])))

var Total = CALCULATE(SUM(Query1[no_of_records]),

  FILTER( Query1, Query1[Start of Week] <= MAX(Query1[Start of Week])))

var Result3 = Result1 - Result2

var main = DIVIDE(Result3 , Total) * 100

Return

main

Any help would be appreciated

Many Thanks in advance

[pm_97 · Accepted solution]

Hi Guys,

Thanks for all your efforts and help.

I have achieved desired result with the help of window() function.

test =
var result1 =
CALCULATE(
(CALCULATE(SUM('Query1'[no_of_records]),('Query1'[age]= 0 || 'Query1'[age]= 1 || 'Query1'[age]= 2 || 'Query1'[age]= 3 || 'Query1'[age]= 4 || 'Query1'[age]= 5 || 'Query1'[age]= 6 ))),
WINDOW(1,ABS,
0,REL,
SUMMARIZE(ALLSELECTED(Query1),Query1[Start of Week])))

var result2 =
CALCULATE(
(CALCULATE(SUM('Query1'[no_of_records]),('Query1'[age]= 0 || 'Query1'[age]= 1 || 'Query1'[age]= 2 || 'Query1'[age]= 3 || 'Query1'[age]= 4 || 'Query1'[age]= 5 || 'Query1'[age]= 6 ))),
WINDOW(1,ABS,
0,REL,
SUMMARIZE(ALLSELECTED(Query1),Query1[Start of Week])))

var result3 = CALCULATE(
SUM(Query1[no_of_records]),
WINDOW(1,ABS,
0,REL,
SUMMARIZE(ALLSELECTED(Query1),Query1[Start of Week])))

var result4 = ROUND(((result1 - result2) / result3 * 100),0)

return
result4

Thanks,
pm_97

[v-yiruan-msft]

@lbendlin Thanks for your contribution on this thread.

Hi @pm_97 ,

Please update the formula of measure [test] as below and check if it can return your expected result...

test =
VAR _week =
    SELECTEDVALUE ( Query1[Start of Week] )
VAR Result1 =
    CALCULATE (
        SUM ( Query1[no_of_records] ),
        FILTER (
            ALLSELECTED ( Query1 ),
            Query1[age]
                IN { 9, 10 }
                    && Query1[Start of Week] <= _week
        )
    )
VAR Result2 =
    CALCULATE (
        SUM ( Query1[no_of_records] ),
        FILTER (
            ALLSELECTED ( Query1 ),
            Query1[age]
                IN { 0, 1, 2, 3, 4, 5, 6 }
                    && Query1[Start of Week] <= _week
        )
    )
VAR Total =
    CALCULATE (
        SUM ( Query1[no_of_records] ),
        FILTER ( ALLSELECTED ( Query1 ), Query1[Start of Week] <= _week )
    )
VAR Result3 = Result1 - Result2
VAR main =
    DIVIDE ( Result3, Total ) * 100
RETURN
    main

If no, please provide the required info just as suggested by @lbendlin. It would be helpful to get the solution. Thank you.

Best Regards

[lbendlin]

Read about the IN operator.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...