How to use isinscope correctly to calculate the average of each category?

[TungNguyen_19]

Hi everyone,

I have this sample data below to calculate average of frequency appears of bills in each category:

B/L Nbrs	REASON	REMARKS	REMARKS.HEAD
MEDUFQ40582	Shipper's Request	AMEND NAC	AMEND
MEDUV965302	COL	AMEND PAYMENT LOCALTION EE ANTWERP	AMEND
MEDUFQ43441	Shipper's Request	AMEND GW	AMEND
MEDUFQ40211	Shipper's Request	AMEND DESCRIPTION	AMEND
MEDUFQ37619	Switch	AMEND SHPR, CNEE, NOTIFY	AMEND
MEDUFQ42926	Shipper's Request	CHANGE SWB	CHANGE
MEDUFQ37837	Shipper's Request	UPDATE SWB	UPDATE
MEDUFQ36389	Tariff Update	ADD DMG CHARGE - SHIPPER REQUEST	ADD
MEDUFQ25061	Tariff Update	MSC AUSTRALIA REQUEST TO REMOVE SPD FEE FOR CUSTOMER- IFF ACCOUNT	MSC
MEDUFQ50474	Tariff Update	UPDATE TO CORRECT RATE - MSC RUSSIA UPDATED TMS AFTER DTX CLOSING	UPDATE
MEDUFQ50648	Shipper's Request	AMEND CNEE, NOTIFY	AMEND
MEDUFQ50647	Shipper's Request	AMEND CNEE, NOTIFY	AMEND
MEDUFQ47525	Shipper's Request	AMEND CTNS	AMEND
MEDUFQ51631	Shipper's Request	ADD FREE TIME AT DEST ON BILL	ADD
MEDUFQ51042	Shipper's Request	REVISE SEAL	REVISE
MEDUFQ52567	Shipper's Request	UPDATE OBL AT DEST	UPDATE
MEDUFQ34475	Shipper's Request	AMEND CNEE+NOTIFY	AMEND
MEDUFQ349435	Shipper's Request	DESCRIPTION	DESCRIPTION
MEDUFQ346795	Shipper's Request	SWB	SWB
MEDUFQ388698	Shipper's Request	UPDAT SWB	UPDAT
MEDUFQ398846	Tariff Update	FILING ERROR	FILING
MEDUFQ425979	Tariff Update	FILING ERROR	FILING
MEDUFQ412167	Tariff Update	FILING ERROR	FILING
MEDUFQ411441	Tariff Update	FILING ERROR	FILING
MEDUFQ410823	Tariff Update	AMD ONC	AMD
MEDUFQ386403	Change of Destination	REMOVE FINAL DESTINATION	REMOVE
MEDUFQ282503	Change of Destination	REMOVE FINAL DESTINATION	REMOVE
MEDUFQ424675	Custom's Request	ADD VENT SEAL	ADD
MEDUFQ410823	Change of Destination	SHIPBACK	SHIPBACK
MEDUFQ306914	Clerical Error	AMD FRT	AMD
MEDUFQ375885	Custom's Request	UPDATE CARGO OF VALUE	UPDATE
MEDUFQ362982	Shipper's Request	UPDATE SWB	UPDATE

⭐ Now I want to calulate average B.L of each category by each level of category: Reason > Remarks.Head > Remarks and I use this measure:

Avg_Per_Reason =
IF(
ISINSCOPE('M EMC Details'[REMARKS])
, DIVIDE([No_Remark_Per_Remark.Head], [All_Remark_Per_Remark.Head], 0)
, IF(ISINSCOPE('M EMC Details'[REMARKS.HEAD])
, DIVIDE([No_Remark.Head_Per_Reason], [All_Remark_Per_Reason], 0)
, IF(ISINSCOPE('M EMC Details'[REASON])
, DIVIDE([All_Remark_Per_Reason], [All_Reason], 0)
, 1
)
)
)

⭐For the other measures, here are all remain measures's fomular:

No_Each_Reason_In_All_Reason = COUNT('M EMC Details'[REASON])

All_Reason = CALCULATE(
COUNT('M EMC Details'[REASON]),
ALLEXCEPT('M EMC Details','M EMC Details'[B/L Nbrs])
)

No_Remark_Per_Remark.Head =
CALCULATE(
COUNT('M EMC Details'[REMARKS]),
ALLEXCEPT('M EMC Details','M EMC Details'[REMARKS.HEAD], 'M EMC Details'[REMARKS])
)
All_Remark_Per_Remark.Head =

Details'[REASON]
CALCULATE(
COUNT('M EMC Details'[REMARKS.HEAD]),
ALLEXCEPT('M EMC Details','M EMC Details'[REASON], 'M EMC Details'[REMARKS.HEAD])
)

No_Remark.Head_Per_Reason =
CALCULATE(
COUNT('M EMC Details'[REMARKS.HEAD]),
ALLEXCEPT('M EMC Details','M EMC Details'[REASON], 'M EMC Details'[REMARKS.HEAD])
)

All_Remark_Per_Reason =
CALCULATE(
COUNT('M EMC Details'[REASON]),
ALLEXCEPT('M EMC Details','M EMC Details'[REASON])
)

⭐ The chart look like this:

The result I expect when I expand to each [Remark.Head]: The accumulate of average of all items Remark in that [Remark.Head] will be 100%. Ex: "REMOVE CONT" in CLC > REMOVE should be 100%

Same with [Reason], sum all [Remark.Head] of each reason will be accumulate to 100%.  One important thing that all [Remark.Head] will be calculate average partition for each reason.

One another thing is when change the date slicer the measure does not reflect the changes!

But my result is mismatch what I expect, can anyone please help me! Many thanks for you guy

[lbendlin]

Something like this?

[TungNguyen_19]

Hi @lbendlin ,
I have tested your measure fomular but some results are not exactly right like this one (Dummy > Update > ...). In details: The [Remark.Head] = UPDATE is 100% is right because Dummy contains only UPDATE. But each [Remark] inside it should be calculated in percentage which is accumulated to 100% (currently each of them is 100%). Can you please double check it?

I can generate the sample data for you for easier checking:

B/L Nbrs	REASON	REMARKS	REMARKS.HEAD
MEDUFQ43753	Dummy	UPDATE TMS CNEE NOTIFY	UPDATE
MEDUFQ31619	Dummy	UPDATE TMS CNEE NOTIFY	UPDATE
MEDUFQ24357	Dummy	UPDATE TMS CNEE NOTIFY	UPDATE
MEDUFQ14683	Dummy	UPDATE TMS OF CNEE	UPDATE
MEDUFQ11076	Dummy	UPDATE CONSIGNEE-NOTIFY- CODE US004070 UPDATE ON 1-SEP AFTER DTX	UPDATE
MEDUFQ10164	Dummy	UPDATE TMS CODE CNEE & CH AFTER DTX CLOSING	UPDATE
MEDUFQ12778	Dummy	UPDATE TMS OF CNEE	UPDATE
MEDUFQ47257	Dummy	UPDATE CNEE	UPDATE
MEDUFQ42511	Dummy	UPDATE TMS CNEE/NOTIFY	UPDATE
MEDUFQ37027	Dummy	UPDATE TMS CNEE, NOTIFY	UPDATE
MEDUFQ37019	Dummy	UPDATE TMS CNEE, NOTIFY	UPDATE
MEDUFQ37947	Dummy	UPDATE TMS CNEE	UPDATE
MEDUFQ36774	Dummy	UPDATE TMS CONSNEE+NOTIFY PARTY	UPDATE
MEDUFQ35339	Dummy	UPDATE TMS CODE CNEE & NOTIFY	UPDATE
MEDUFQ32677	Dummy	UPDATE TMS CNEE & NOTIFY	UPDATE
MEDUFQ29403	Dummy	UPDATE TMS NOTIFY PARTY	UPDATE
MEDUFQ28027	Dummy	UPDATE TMS NOTIFY PARTY 2	UPDATE
MEDUFQ22459	Dummy	UPDATE TMS NOTIFY PARTY 2	UPDATE
MEDUFQ27647	Dummy	UPDATE TMS CNEE NOTIFY	UPDATE
MEDUFQ27647	Dummy	UPDATE TMS CNEE NOTIFY	UPDATE
MEDUFQ27642	Dummy	UPDATE TMS CNEE NOTIFY	UPDATE
MEDUFQ24512	Dummy	UPDATE TMS CNEE	UPDATE
MEDUFQ27080	Dummy	UPDATE TMS FOR CNEE/NOTIFY	UPDATE
MEDUFQ16641	Dummy	UPDATE TMS CODE FOR CNEE/NOTIFY	UPDATE
MEDUFQ17375	Dummy	UPDATE TMS CODE CNEE & NOTIFY	UPDATE
MEDUFQ17451	Dummy	UPDATE TMS CODE FOR NOTIFY 2	UPDATE
MEDUFQ23476	Dummy	UPDATE TMS CONSINGEE/ NOTIFY	UPDATE

[lbendlin]

Here is what I get based on your new sample data:

[TungNguyen_19]

Hi @lbendlin 
- Sorry that I have only sent you a part of Dummy. If you see the picture below you will find all the [Remark.Head] "Dummy" which contains 8 items. 

- Let's take an example UPDATE which is 29 records. The result of measure hear should be 29/51 = 57% but the current returns from your fomular is 82,29%. Also WISE can not be 100%...
- I also want to note that other [Reason] can have the same [Remark.Head] too (you can see in the picture attached with).
* Note that: The result should be reflected with the Date filter too. I have tested the numerator and it return true, I think the problem come from denominator but I'm not sure what is the problem here.

Can you please check it again for my case? If you need any information you can ask me

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[TungNguyen_19]

Hi @lbendlin 

This is my full cover issue sample data

Today when I try to paste the matrix to another tab it return correct data which I believe the error is related to the filter slicer date.

1/ With slicer it returns 100% for all [REMARK] items:

2/ Without slicer it returns correct average metrics:

Can you please fix the fomular to return correct data with the slicer filter because i have to import this filter to view data from any point of time? Many thanks sir

[lbendlin]

for some reason REMOVEFILTERS is misbehaving.  Here is an alternative implementation

In Category = 
var rs = max('Table'[REASON])
var rh = max('Table'[REMARKS.HEAD])
var a = switch(TRUE(),
ISINSCOPE('Table'[REMARKS]),CALCULATE(COUNTROWS('Table'),ALLSELECTED('Table'),'Table'[REASON]=rs,'Table'[REMARKS.HEAD]=rh),
ISINSCOPE('Table'[REMARKS.HEAD]),CALCULATE(COUNTROWS('Table'),ALLSELECTED('Table'),'Table'[REASON]=rs),
CALCULATE(COUNTROWS('Table'),ALLSELECTED('Table')))
return divide(COUNTROWS('Table'),a,0)

[lbendlin]

Even that is insufficient.

In Category = 
var rs = max('Table'[REASON])
var rh = max('Table'[REMARKS.HEAD])
var d = ALLSELECTED('Table'[DATE])
var a = switch(TRUE(),
ISINSCOPE('Table'[REMARKS]),CALCULATE(COUNTROWS('Table'),ALLselected('Table'),d,'Table'[REASON]=rs,'Table'[REMARKS.HEAD]=rh),
ISINSCOPE('Table'[REMARKS.HEAD]),CALCULATE(COUNTROWS('Table'),ALLSELECTED('Table'),d,'Table'[REASON]=rs),
CALCULATE(COUNTROWS('Table'),ALLSELECTED('Table'),d))
return  divide(COUNTROWS('Table'),a,0)

[TungNguyen_19]

Thanks @lbendlin 
- The fomular works perfectly ❤️ But can I ask that why you use all these filters in each CALCULATE scope - This fomular is really complicated for me and I have no clues how does it worked? 😢

- If you don't mind please explain how your solution worked in details and I will use this sample for the completed tutorial for this function on my blog (which refer to your answer) for anyone else face to this problem! I think this will help a lot for many people.

[lbendlin]

REMOVEFILTERS worked as designed but the complexity came from the date range slicer. Once that is taken care of the original formula works as well

InCategory =
-- grab the date range for the entire table so we can reapply it for the individual items
VAR d =
    ALLSELECTED ( 'Table'[DATE] ) 
-- get the total number of entries for all siblings in the current scope level
VAR a =
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Table'[REMARKS] ), CALCULATE ( COUNTROWS ( 'Table' ), d, REMOVEFILTERS ( 'Table'[REMARKS] ) ),
        ISINSCOPE ( 'Table'[REMARKS.HEAD] ), CALCULATE ( COUNTROWS ( 'Table' ), d, REMOVEFILTERS ( 'Table'[REMARKS.HEAD] ) ),
        CALCULATE ( COUNTROWS ( 'Table' ), d, REMOVEFILTERS ( 'Table'[REASON] ) )
    ) 
-- percentage of current item number of row versus parent number of rows
RETURN
    DIVIDE ( COUNTROWS ( 'Table' ), a, 0 )

The pattern is called "Filtering Up"  when you take an individual item and then go up one level in the hierarchy to collect all items/siblings. That is a workaround for the missing "Percentage of Parent Total"  implicit measure

One other thing worth mentioning is that we take advantage of the exit feature in SWITCH - it stops when it found a match.  We start from the lowest hierarchy level and stop once we find a level that is in scope (ie that is rendered in the matrix).

[TungNguyen_19]

Hi @lbendlin, 

Thanks for your explains but only your lasted solution version(*) works in my case and I don't understand why you use these filters like:

, ALLSELECTED('Table')
, ALLSELECTED('Table'[DATE])

, 'Table'[REASON] = MAX('Table'[REMARKS.HEAD])
, 'Table'[REMARKS.HEAD] = MAX('Table'[REMARKS.HEAD])

* Your worked solution:

VAR _total = SWITCH(TRUE(),
    ISINSCOPE('Table'[REMARKS]),
        CALCULATE(COUNTROWS('Table')
            , ALLSELECTED('Table')
            , ALLSELECTED('Table'[DATE])
            , 'Table'[REASON] = MAX('Table'[REMARKS.HEAD])
            , 'Table'[REMARKS.HEAD] = MAX('Table'[REMARKS.HEAD])
        ),
    ISINSCOPE('Table'[REMARKS.HEAD])
        ,CALCULATE(COUNTROWS(Table')
            , ALLSELECTED('Table')
            , ALLSELECTED('Table'[DATE])
            , 'M EMC Details'[REASON]= MAX('Table'[REASON])
        ),
    CALCULATE(COUNTROWS('Table')
        , ALLSELECTED('Table')
            , ALLSELECTED('Table'[DATE])
        )
    )

RETURN  DIVIDE(COUNTROWS('Table'), _total, 0)

Many thanks to you

[lbendlin · Accepted solution]

You modified my version in a way that I don't agree with. Don't use multiple ALLSELECTED. Don't run multiple identical calculations (use variables instead).

PLease use this version

InCategory =
-- grab the date range for the entire table so we can reapply it for the individual items
VAR d =
    ALLSELECTED ( 'Table'[DATE] ) 
-- get the total number of entries for all siblings in the current scope level
VAR a =
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Table'[REMARKS] ), CALCULATE ( COUNTROWS ( 'Table' ), d, REMOVEFILTERS ( 'Table'[REMARKS] ) ),
        ISINSCOPE ( 'Table'[REMARKS.HEAD] ), CALCULATE ( COUNTROWS ( 'Table' ), d, REMOVEFILTERS ( 'Table'[REMARKS.HEAD] ) ),
        CALCULATE ( COUNTROWS ( 'Table' ), d, REMOVEFILTERS ( 'Table'[REASON] ) )
    ) 
-- percentage of current item number of row versus parent number of rows
RETURN
    DIVIDE ( COUNTROWS ( 'Table' ), a, 0 )

[TungNguyen_19]

Thank you @lbendlin 

That is the long journey and you help me a lot to understand this topic. Many thanks to you ❤️

[shoptinhyeuvn]

Your solution is very good.