Forum Discussion

AshuKumar011's avatar
AshuKumar011
New Member
2 years ago
Solved

Multiple Slicer based Output

i have legal Entity column which i can use it as multiple select slice i have to create one count rows card in which i have to show count the no person belong to that particular slicer selection eith...
Need Help
Show and Tell
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    Hello AshuKumar011,

     

    Can you please try this approach:

     

    1. Create a measure to count the number of persons

    PersonCount = 
CALCULATE(
    COUNTROWS('YourTableName'),
    ALLSELECTED('YourTableName'[Legal Entity])
)

    2. Create Measures for Each Quartile

    PersonCountDiv4 = DIVIDE([PersonCount], 4, 0)

Modulus = MOD([PersonCount], 4)

Quartile1 = 
SWITCH(
    TRUE(),
    [Modulus] = 0, [PersonCountDiv4],
    [Modulus] = 1, [PersonCountDiv4] + 1,
    [Modulus] = 2, [PersonCountDiv4],
    [Modulus] = 3, [PersonCountDiv4] + 1,
    0
)

Quartile2 = 
SWITCH(
    TRUE(),
    [Modulus] = 0, [PersonCountDiv4],
    [Modulus] = 1, [PersonCountDiv4],
    [Modulus] = 2, [PersonCountDiv4],
    [Modulus] = 3, [PersonCountDiv4] + 1,
    0
)

Quartile3 = 
SWITCH(
    TRUE(),
    [Modulus] = 0, [PersonCountDiv4],
    [Modulus] = 1, [PersonCountDiv4],
    [Modulus] = 2, [PersonCountDiv4] + 1,
    [Modulus] = 3, [PersonCountDiv4] + 1,
    0
)

Quartile4 = 
SWITCH(
    TRUE(),
    [Modulus] = 0, [PersonCountDiv4],
    [Modulus] = 1, [PersonCountDiv4],
    [Modulus] = 2, [PersonCountDiv4],
    [Modulus] = 3, [PersonCountDiv4],
    0
)

    3. Create a Table and display the Results

    Quartiles = DATATABLE(
    "Quartile", STRING,
    { {"Q1"}, {"Q2"}, {"Q3"}, {"Q4"} }
)

    QuartileCount = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(Quartiles[Quartile]) = "Q1", [Quartile1],
    SELECTEDVALUE(Quartiles[Quartile]) = "Q2", [Quartile2],
    SELECTEDVALUE(Quartiles[Quartile]) = "Q3", [Quartile3],
    SELECTEDVALUE(Quartiles[Quartile]) = "Q4", [Quartile4],
    0
)

    Hope this helps.

Sahir_Maharaj's avatar
Sahir_Maharaj
Super User
2 years ago

Hello AshuKumar011,

 

Can you please try this approach:

 

1. Create a measure to count the number of persons

PersonCount = 
CALCULATE(
    COUNTROWS('YourTableName'),
    ALLSELECTED('YourTableName'[Legal Entity])
)

2. Create Measures for Each Quartile

PersonCountDiv4 = DIVIDE([PersonCount], 4, 0)

Modulus = MOD([PersonCount], 4)

Quartile1 = 
SWITCH(
    TRUE(),
    [Modulus] = 0, [PersonCountDiv4],
    [Modulus] = 1, [PersonCountDiv4] + 1,
    [Modulus] = 2, [PersonCountDiv4],
    [Modulus] = 3, [PersonCountDiv4] + 1,
    0
)

Quartile2 = 
SWITCH(
    TRUE(),
    [Modulus] = 0, [PersonCountDiv4],
    [Modulus] = 1, [PersonCountDiv4],
    [Modulus] = 2, [PersonCountDiv4],
    [Modulus] = 3, [PersonCountDiv4] + 1,
    0
)

Quartile3 = 
SWITCH(
    TRUE(),
    [Modulus] = 0, [PersonCountDiv4],
    [Modulus] = 1, [PersonCountDiv4],
    [Modulus] = 2, [PersonCountDiv4] + 1,
    [Modulus] = 3, [PersonCountDiv4] + 1,
    0
)

Quartile4 = 
SWITCH(
    TRUE(),
    [Modulus] = 0, [PersonCountDiv4],
    [Modulus] = 1, [PersonCountDiv4],
    [Modulus] = 2, [PersonCountDiv4],
    [Modulus] = 3, [PersonCountDiv4],
    0
)

3. Create a Table and display the Results

Quartiles = DATATABLE(
    "Quartile", STRING,
    { {"Q1"}, {"Q2"}, {"Q3"}, {"Q4"} }
)

QuartileCount = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(Quartiles[Quartile]) = "Q1", [Quartile1],
    SELECTEDVALUE(Quartiles[Quartile]) = "Q2", [Quartile2],
    SELECTEDVALUE(Quartiles[Quartile]) = "Q3", [Quartile3],
    SELECTEDVALUE(Quartiles[Quartile]) = "Q4", [Quartile4],
    0
)

Hope this helps.