Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Identifying relevant local markets

Hi,

 

I'm working for a company that define the local market of a branch/shop as all local authorities that represent at least 80% of customers.

 

For the below examples:

1. if a branch has one local authority giving 67%, another giving 12% and a third giving 8% then all other local authorities can be ignored because 80% has been identified.

2. if a branch has one local authority giving 70% and three local authorities giving 8% then all 4 local authorities should be included because it was a tie for second place.

 

Please can you tell me how to identify the relevant local authorities using a calculated measure? I'm trying to avoid using a calculated column so that the user can change the time periods using a slicer.

 

 

Thanks for any pointers,

 

CM

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    One sample for your reference. Here I created two calculated columns to work on it. Please notice I modified your data a bit to make the logic more reasonable.

     

    INT = 
VAR total =
    CALCULATE (
        SUM ( [%of customers] ),
        FILTER (
            Table1,
            Table1[Branch Number] = EARLIER ( Table1[Branch Number] )
                && Table1[Local AN] <= EARLIER ( Table1[Local AN] )
        )
    )
VAR no =
    INT ( total * 10 )
RETURN
    no

    Result = 
VAR cou =
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            Table1[Branch Number] = EARLIER ( Table1[Branch Number] )
                && Table1[Local AN] <= EARLIER ( Table1[Local AN] )
                && Table1[INT] = 8
        )
    )
RETURN
    IF ( Table1[INT] <= 8 && cou <> 2, 1, 0 )

     

    Pbix as attached.

     

1 Reply

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous ,

     

    One sample for your reference. Here I created two calculated columns to work on it. Please notice I modified your data a bit to make the logic more reasonable.

     

    INT = 
VAR total =
    CALCULATE (
        SUM ( [%of customers] ),
        FILTER (
            Table1,
            Table1[Branch Number] = EARLIER ( Table1[Branch Number] )
                && Table1[Local AN] <= EARLIER ( Table1[Local AN] )
        )
    )
VAR no =
    INT ( total * 10 )
RETURN
    no

    Result = 
VAR cou =
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            Table1[Branch Number] = EARLIER ( Table1[Branch Number] )
                && Table1[Local AN] <= EARLIER ( Table1[Local AN] )
                && Table1[INT] = 8
        )
    )
RETURN
    IF ( Table1[INT] <= 8 && cou <> 2, 1, 0 )

     

    Pbix as attached.