Calculated Measure Calculation Using Other Calculated Measures

[Nozo-3]

Hi All!

I've just start using Power BI.

Now, I'm totally stuck.

For making the Evaluation Report for Call Center, a rawdata table is like below;

"Table"

Call ID   No. of Week   Operator   Opening Completion   Closing Completion  ......

aaa001         45                  aaa                        0                                  1

aaa002         45                  bbb                       1                                  1

aaa003         46                  ccc                         0                                 0

aaa004         46                  aaa                        1                                  0

aaa005         46                  ccc                         1                                  1

aaa006         47                  bbb                        0                                 1

aaa007         47                  aaa                         0                                 1

*Completion : 1=Completed  0=Not Completed

Then, all I have to do is;

1. Need to calculate the completion rate

"Completion Rate of Opening Completion"

CR_OP = SUM([Opening Completion])/COUNT([Opening Completion])

2. Dynamically changes of report by week

Page/Report level Filter = No. of Week

3. Need to calculate the completion rate of previous week

"Completion Rate of Opening Completion(previous week)"

For calculate  the completion rate of previous week, I did four steps below;

 0) Calcurate the "Opening Completion Points"

Pts_OP = CALCULATE(SUM('Table'[Opening Completion]), ALLEXCEPT(Table, Table[No. of Week],Table[Opening Completion]))

 1) Calcurate the "Previous week Opening Completion Points" 

Pts_OP_PW = CALCULATE(SUM('Table'[Opening Completion]), FILTER(ALL(Table), SUMX(Filter('Table', 'Table'[No. of Week] = EARLIER(Table[No. of Week]) + 1), 'Table'[Pts_OP])))

2)  Count the No. of "Previous week Opening Completion"

No._OP_PW = CALCULATE(COUNT(Table[No. of Week]),FILTER(ALL(Table), SUMX(Filter('Table', 'Table'[No. of Week] = EARLIER(Table[No. of Week]) + 1), Table[No. of Week])))

3) Calculate the completion rate of previous week

CR_OP_PW = DEVIDE([Pts_OP_PW],[No._OP_PW])

However, the result of Calculation 3) scores "0"or"1" depending on the selection of "Week No.".

Please give me some ideas to solve this case.

Thanks in advance.

[v-huizhn-msft · Accepted solution]

Hi @Nozo-3,

I suggest you create a slicer including [No. of Week], when you select a value in the slicer, you can create a measure to get the selected value using the formula. You can review here to check the function SELECTEDVALUE.

select_value=SELECTEDVALUE(Table[No. of Week])

Then you can replace the measure in your formula as the follows(bold), and check if it works fine.

Pts_OP_PW =
CALCULATE (
    SUM ( 'Table'[Opening Completion] ),
    FILTER (
        ALL ( Table ),
        SUMX (
            FILTER ( 'Table', 'Table'[No. of Week] = [selected_value] - 1 ),
            'Table'[Pts_OP]
        )
    )
)

No._OP_PW =
CALCULATE (
    COUNT ( Table[No. of Week] ),
    FILTER (
        ALL ( Table ),
        SUMX (
            FILTER ( 'Table', 'Table'[No. of Week] = [selected_value] - 1 ),
            Table[No. of Week]
        )
    )
)

Best Regards,
Angelia

[Nozo-3]

Thank you very much for your reply,@v-huizhn-msft,

but it seems I need more help from you.

I created "select_value" as a new measure and tried to replace the measure in my formula, but the "select_value" is not shown as effective references.

Actually, "No. of Week" is also created measure so does it bother the calculation and formula???

[v-huizhn-msft]

Hi @Nozo-3,

Based on my understanding, [No. of Week] a column in your resource table. You create a card visual, just add the [selected_value] measure in to check if it display the right value. Or you can share your .pbix file for further analysis.

Best Regards,
Angelia

[Nozo-3]

Hi Angelia,@v-huizhn-msft,

Thank you very much for your reply again and 

Sorry to be late of my reply because of National Holiday of Japan.

>Based on my understanding, [No. of Week] a column in your resource table. You create a card visual, just add the [selected_value] measure in to check if it display the right value. 

Your understanding is almost right.

Actually all I want to do at this moment is;

1. Based on [No. of Week] as a Filter

2. State the [Average Copletion Rate] for All evaluation categories like [Opening], [Closing], and the others of the week

3. State the [Copletion Rate] for Each of the evaluation categories like [Opening], [Closing], and the others of the week

4. State the [Average Copletion Rate] of Previous week

5. State the [Copletion Rate] of Previous week

6. State the difference between the week and previous week for the sake that the evaluation categories of Call Center could be improved or not, comparing previous week

All of the above could be stated as Card Visuals.

Then, other dashboard will be stated "Each Operators' Evaluation" drilled down to each [Call ID].

All of rawdata, dashboard, and formula is written in Japanese so sharing my .pbix would be taken time to translate.

I hope that above the information helps you to understand my situation.

If not, please let me know.  I'll make the English version of dashboard.

Thanks and Best Regards.

Nozomu

[Nozo-3]

Hi Angelia,@v-huizhn-msft,

I'm so sorry to take time to reply.

As a result, the formula that you mentioned is totally fine.  It surely worked from the beggining.

The point that I had to correct was the "% sign format" of the measure.

As I didn't set the "% sign format" of the measure, the calculated result was automattically rounded.

That is why the calculated result was "0" or "1".

Thank you very much for your help!

Best Regards;

Nozomu