Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to create a Quadrant with concatenated row values

Hi, i'm trying to recreate this chart and i'm struggling to work out the best way to do it. i've tried using a scatter chart but it isn't giving me quite what i need. the real dataset has a lot more values, and when there are a lot of names, they overlap and also some get missed off the chart. i'm thinking about concatenating and comma separating the rep names and creating a table for each of the 16 quadrants, but not sure that's the best way and i've not been able to get the concatenatex to work which i think i need for that. 

i could go with the quadrant if all else fails but the format below is what is really wanted.

 

thanks in advance

 

 

Sales RepHome Sales/Target %Oseas Sales/Target %
Dan100
Pete2510
Kerry3010
Hannah108150
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    Maybe you can do like this.

    % Sales/Target (Home) = 
IF(
    [Home/Oseas] = "Home",
    DIVIDE(
        Sheet3[Sales], Sheet3[Target],
        0
    )
)
    % Sales/Target (Oseas) = 
IF(
    [Home/Oseas] = "Oseas",
    DIVIDE(
        Sheet3[Sales], Sheet3[Target],
        0
    )
)
    bucket 1 = 
SWITCH(
    TRUE(),
    [% Sales/Target (Home)] <50, " 0 - 50%",
    [% Sales/Target (Home)] <75, " 50 - 74%",
    [% Sales/Target (Home)] < 100," 75 - 99%",
    [% Sales/Target (Home)] >100, "100+%"
)
    bucket 2 = 
SWITCH(
    TRUE(),
    [% Sales/Target (Oseas)] <50, " 0 - 50 %",
    [% Sales/Target (Oseas)] <75, " 50 - 74 %",
    [% Sales/Target (Oseas)] < 100," 75 - 100 %",
    [% Sales/Target (Oseas)] >100, "100+ %"
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , you will not get the same visual, create two buckets like

    bucket 1 =
    switch(true(),
    [Home Sales/Target %] <50, " 0 - 50 %",
    [Home Sales/Target %] <75, " 50 - 74 %",
    [Home Sales/Target %] < 100," 75 - 100 %",
    [Home Sales/Target %] >100, "100+ %")

     

    bucket 2 =
    switch(true(),
    [Oseas Sales/Target %] <50, " 0 - 50 %",
    [Oseas Sales/Target %] <75, " 50 - 74 %",
    [Oseas Sales/Target %] < 100," 75 - 100 %",
    [Oseas Sales/Target %] >100, "100+ %")

     

    Put them on matrix row and column and take max of name.

     

    In case thiese %'s are measures

    refer

    SEGMENTATION

    https://www.daxpatterns.com/dynamic-segmentation/
    https://www.daxpatterns.com/static-segmentation/
    https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
    https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Many thanks for the replies. i'll have a look and let you know how it goes.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, i'm having a problem creating 2 buckets - i'm getting a circular dependency error with the 2nd bucket. The Home Sales/Target% and Oseas Sales/Target% are actually calculated from the Sales which has rows for Home and Oseas for each rep. Data modelled like this:

     

    RepSalesTargetHome/Oseas
    A1015Home
    A155Oseas
    B6020Home
    C55Home
    C1015Oseas


    This is how i get the % Sales/Target measure:

    % Sales/Target (Home) = CALCULATE(IFERROR([Sum Sales] / [Sum Target], 0), 'Sales vs Targets'[Home/Oseas] = "Home")

    % Sales/Target (Oseas) = CALCULATE(IFERROR([Sum Sales] / [Sum Target], 0), 'Sales vs Targets'[Home/Oseas] = "Oseas")

     

    If i now try to create the buckets i get the circular dependency error, i guess because both are using the same Sales and Targets fields as their source. i've tried creating calculated fields: Sales Home; Sales Oseas; Target Home; Target Oseas conditionally using the Home/Oseas column but hit the same issue. Any ideas how i can get around this? I think the solutions posted will work if i can get these buckets right.

     

    many thanks for your help

     

    • v-lionel-msft's avatar
      v-lionel-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Anonymous ,

       

      Maybe you can do like this.

      % Sales/Target (Home) = 
IF(
    [Home/Oseas] = "Home",
    DIVIDE(
        Sheet3[Sales], Sheet3[Target],
        0
    )
)
      % Sales/Target (Oseas) = 
IF(
    [Home/Oseas] = "Oseas",
    DIVIDE(
        Sheet3[Sales], Sheet3[Target],
        0
    )
)
      bucket 1 = 
SWITCH(
    TRUE(),
    [% Sales/Target (Home)] <50, " 0 - 50%",
    [% Sales/Target (Home)] <75, " 50 - 74%",
    [% Sales/Target (Home)] < 100," 75 - 99%",
    [% Sales/Target (Home)] >100, "100+%"
)
      bucket 2 = 
SWITCH(
    TRUE(),
    [% Sales/Target (Oseas)] <50, " 0 - 50 %",
    [% Sales/Target (Oseas)] <75, " 50 - 74 %",
    [% Sales/Target (Oseas)] < 100," 75 - 100 %",
    [% Sales/Target (Oseas)] >100, "100+ %"
)

       

      Best regards,
      Lionel Chen

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi all, has anyone got any suggestions how to get around issue i have with creating the 2 calculated columns as suggested in the 2 posts above? this crosstab will work for me if i can get that working but i'm really struggling with the circular dependency when creating the 2nd bucket column. i'm newish to pbi so might be missing a simple solution. 

    Alternatively i am happy to use a scatter chart which does work with the measures i've created but that misses out rep name labels where they are closely clustered or too near to an axis, and i need to be able to see all names. any help greatly appreciated as i've spent a while going round in (dependency) circles 🙂

     

    thanks