How to create a Quadrant with concatenated row values

[Anonymous]

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 Rep	Home Sales/Target %	Oseas Sales/Target %
Dan	10	0
Pete	25	10
Kerry	30	10
Hannah	108	150

[Anonymous]

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

[Anonymous]

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:

Rep	Sales	Target	Home/Oseas
A	10	15	Home
A	15	5	Oseas
B	60	20	Home
C	5	5	Home
C	10	15	Oseas

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 · Accepted solution]

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]

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

[v-lionel-msft]

Hi @Anonymous ,

Please refer to my .pbix file.

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.

[amitchandak]

@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