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 either one value is selected or multiple . now i have to create one mod that gives me the value that is divided by 4 now need one measure that dive the value if mod is 0 then  count /4 and result should be q1 = count /4 ,q2 = count /4 ,q2= count /4 ,q3 = count /4, q4 = count /4, if mod 1 result should be q1 = (count /4)+1 ,q2 = count /4 ,q2= count /4 ,q3 = count /4, q4 = count /4, if mod 2 result should be q1 = (count /4) ,q2 = count /4 ,q2= (count /4)+1 ,q3 = count /4, q4 = count /4, if mod 3 result should be q1 = (count /4)+1 ,q2 = (count /4)+1 ,q2= (count /4)+1 ,q3 = count /4, q4 = count /4, i need to show the table format  Quartile and count of genders. as per the above calculations

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.

2 Replies

  • 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.