Slicer to change product user

[weilai0521]

Hi, 

I have a sample data like this. And I need to create a slicer to choose different product users. 

Sample Data

I am working on creating a slicer, which can list the types of products and as I select, it can generate histograms of gender, age, etc. 

For example, if I select 'Cigarette', I want to show the demographic breakdown of cigarette smokers. If I choose 'Cigarette' AND 'e-vapor', I can see the demographic statistics for people who are smoking and using e-vapor.

[v-juanli-msft · Accepted solution]

Hi @weilai0521 

1. unpivot column in Query editor

name column"Attribute"->"product"

2. add a custom column

Close&&apply

3.create a calculated column

age range =
SWITCH (
    TRUE (),
    [Age] >= 18
        && [Age] <= 24, "18~24",
    [Age] >= 18
        && [Age] <= 24, "18~24",
    [Age] >= 25
        && [Age] <= 34, "25~34",
    [Age] >= 35
        && [Age] <= 44, "35~44",
    [Age] >= 45
        && [Age] <= 54, "45~54",
    [Age] >= 55
        && [Age] <= 64, "55~64",
    [Age] >= 65, "65+"
)

4.create measures

age/each =
CALCULATE (
    DISTINCTCOUNT ( Sheet6[ID] ),
    FILTER (
        ALLSELECTED ( Sheet6 ),
        Sheet6[age range] = MAX ( Sheet6[age range] )
            && Sheet6[number] = 1
    )
)


age/total =
CALCULATE (
    DISTINCTCOUNT ( Sheet6[ID] ),
    FILTER ( ALLSELECTED ( Sheet6 ), Sheet6[number] = 1 )
)


%/age = [age/each]/[age/total]

gender/each =
CALCULATE (
    DISTINCTCOUNT ( Sheet6[ID] ),
    FILTER (
        ALLSELECTED ( Sheet6 ),
        Sheet6[Gender] = MAX ( Sheet6[Gender] )
            && Sheet6[number] = 1
    )
)

gender/total =
CALCULATE (
    DISTINCTCOUNT ( Sheet6[ID] ),
    FILTER ( ALLSELECTED ( Sheet6 ), Sheet6[number] = 1 )
)

%/gender = [gender/each]/[gender/total]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft · Accepted solution]

Hi @weilai0521 

1. unpivot column in Query editor

name column"Attribute"->"product"

2. add a custom column

Close&&apply

3.create a calculated column

age range =
SWITCH (
    TRUE (),
    [Age] >= 18
        && [Age] <= 24, "18~24",
    [Age] >= 18
        && [Age] <= 24, "18~24",
    [Age] >= 25
        && [Age] <= 34, "25~34",
    [Age] >= 35
        && [Age] <= 44, "35~44",
    [Age] >= 45
        && [Age] <= 54, "45~54",
    [Age] >= 55
        && [Age] <= 64, "55~64",
    [Age] >= 65, "65+"
)

4.create measures

age/each =
CALCULATE (
    DISTINCTCOUNT ( Sheet6[ID] ),
    FILTER (
        ALLSELECTED ( Sheet6 ),
        Sheet6[age range] = MAX ( Sheet6[age range] )
            && Sheet6[number] = 1
    )
)


age/total =
CALCULATE (
    DISTINCTCOUNT ( Sheet6[ID] ),
    FILTER ( ALLSELECTED ( Sheet6 ), Sheet6[number] = 1 )
)


%/age = [age/each]/[age/total]

gender/each =
CALCULATE (
    DISTINCTCOUNT ( Sheet6[ID] ),
    FILTER (
        ALLSELECTED ( Sheet6 ),
        Sheet6[Gender] = MAX ( Sheet6[Gender] )
            && Sheet6[number] = 1
    )
)

gender/total =
CALCULATE (
    DISTINCTCOUNT ( Sheet6[ID] ),
    FILTER ( ALLSELECTED ( Sheet6 ), Sheet6[number] = 1 )
)

%/gender = [gender/each]/[gender/total]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[weilai0521 · Accepted solution]

Thanks for the details! The unpivot function really helps and I get everything worked out well! I did not find the 'Accept as solution' button, I will check back later to see if I can your post as solution.