Multiple columns needed in switch function.

[SteveCS]

Hi, I have matrix that  shows a breakdown of days, using the following switch() function

Aging_At_Activity Split =

   VAR DaysDifference = 'NurseTAT'[Aging_At_Activity]

   //AGCI.RECEIVED_DATE,ACT.[CREATED_ON]

   RETURN

       SWITCH(

           TRUE(),

           DaysDifference <= 0, "1< day",

           DaysDifference >= 1 && DaysDifference <= 3, "1-3 days",

           DaysDifference >= 4 && DaysDifference <= 6, "4-6 days",

           DaysDifference >= 7 && DaysDifference <= 9, "7-9 days",

           DaysDifference > 9 , "9> days"

       )

I use the count function to show how many are in each bucket.  But I'm trying to find a way to add in an additional field to this switch, so that each columns shows a breakdown by % against the total, as well?
I've tried building it but I keep running into errors.

Help is appreciated.
Thanks, SteveCS

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Should the denominator be the column total, row total or the grand total?

[SteveCS]

So basically You cannot create multiple columns of different data sources using the switch....You can only work with one field at a time (other than using multiple fields in parameters.

[Akash_Varuna]

Hi @SteveCS Please try these out 

1. Create a measure for the total count:

   Total Count = COUNTROWS('YourTableName')

2. Keep your Aging_At_Activity Split calculation as is.

3. Create a measure for each bucket's count:

   Count Bucket Days = 
   CALCULATE(
       COUNTROWS('YourTableName'),
       'YourTableName'[DaysDifference] >= 1 && 'YourTableName'[DaysDifference] <= 3
   )

4. Create percentage measures for each bucket:

   Percent Bucket Days = 
   DIVIDE([Count 1-3 Days], [Total Count], 0)

5. Add the counts and percentages to your matrix.

[SteveCS]

Thank for this. But does this allow me to have a matrix where the columns visually appear as 

Count | Percentage| Count | Percentage | count | percentage | total 

[Akash_Varuna]

You will have to create a custom table along with the above and then union all the above into that table