Power Bi Metrics Table Yes, No and Leave

[DEEW]

Hello, I need to create a power bi metrics table to display as below. I have day of month column. in this example i only have for 12 days, but my Day of Month is from 01st day of the month to the last day of the month based on the month filter.

I need to show Yes , No and Leave and Total to only be Sum of Yes, Appreciate any help. Yes , No, Leave can either be shown by 1,0, 2

1

2

3

4

5

6

7

8

9

10

11

12

Total Yes

Employee A

Leave

Leave

Leave

Yes

Yes

Yes

Yes

Yes

Yes

No

No

No

6

Employee B

No

No

No

No

No

No

Yes

Yes

Yes

Yes

Yes

Leave

5

Employee C

Yes

Yes

Yes

Yes

Yes

Leave

Leave

Leave

Leave

Yes

Yes

Yes

8

[RossEdwards · Accepted solution]

Ok great, that means my original measure would be the correct measure.   That is always handled in the "SELECTEDVALUE" part of the measure.  The countrows part is just to calculate the total.  I suspect you want this:

M_MetricsMeasure = IF(
    COUNTROWS('Table_by_calendar_Cat1') = 1,
    SELECTEDVALUE('Table_by_calendar_Cat1'[Result]),
    FORMAT(
		CALCULATE(
			COUNTROWS('Table_by_calendar_Cat1'),
			'Table_by_calendar_Cat1'[Result] = "Yes"
		), 
	"0")
)

which will have an output like this:

[DEEW · Accepted solution]

OMG, Perfect , it worked a treat,

Thank you so much Ross. 

[DEEW]

Hello Ross, 

• Yes I am using a metrics table
• Yes rows are Employee Column
• Yes, Columns are Day of Month
• Yes I have the metrics's total column on
• I have modified the meature to the below, 
  M_MetrcisMeasure =

  IF (

      COUNTROWS('Table_by_calendar_Cat1') = 1,

      SELECTEDVALUE('Table_by_calendar_Cat1'[Status]),

      CALCULATE (

          SUMX (

              ALLEXCEPT('Table_by_calendar_Cat1', 'Table_by_calendar_Cat1'[R3445_EmployeeId]),

              SWITCH (

                  TRUE(),

                  'Table_by_calendar_Cat1'[Status] = "Yes", 1,

                  'Table_by_calendar_Cat1'[Status] = "Leave", 2,

                  0

              )

          )

      )

  )

  This displays Yes, No, and leave, but does not caculate the sum of "Yes". are you able to help me modify this so the total only shows Sum of "Yes"

[RossEdwards]

What has me confused in your measure is the setting Leave as 2.  This seems to indicate that you want Leave to count as a twice as much compared to Yes.  So a table of "Employee A: Yes, No, No, Leave" would equal 3?

I'm also confused why you need the all except?  

I'd go with:

M_MetrcisMeasure = IF(
    COUNTROWS('Table_by_calendar_Cat1') = 1,
    SELECTEDVALUE('Table_by_calendar_Cat1'[Result]),
    FORMAT(
		CALCULATE(
			COUNTROWS('Table_by_calendar_Cat1'),
			'Table_by_calendar_Cat1'[Result] = "Yes"
		) + (CALCULATE(
			COUNTROWS('Table_by_calendar_Cat1'),
			'Table_by_calendar_Cat1'[Result] = "Leave"
		) * 2), 
	"0")
)

[DEEW]

Sorry for the confusion, I needed to show leave days in my metrics table so i created a Leave = 2, however i dont need the sum of Leave, the total to only be Sum of "Yes"

[RossEdwards · Accepted solution]

Ok great, that means my original measure would be the correct measure.   That is always handled in the "SELECTEDVALUE" part of the measure.  The countrows part is just to calculate the total.  I suspect you want this:

M_MetricsMeasure = IF(
    COUNTROWS('Table_by_calendar_Cat1') = 1,
    SELECTEDVALUE('Table_by_calendar_Cat1'[Result]),
    FORMAT(
		CALCULATE(
			COUNTROWS('Table_by_calendar_Cat1'),
			'Table_by_calendar_Cat1'[Result] = "Yes"
		), 
	"0")
)

which will have an output like this:

[DEEW]

One more help please, how do i show 0 when there is no "Yes" for an employee, Example in your table, Employee B Total to show 0

[RossEdwards · Accepted solution]

We can add a +0 in the count.  This can have unintended consequences as it will come up with zero for every possible outcome.

M_MetricsMeasure = IF(
    COUNTROWS('Table_by_calendar_Cat1') = 1,
    SELECTEDVALUE('Table_by_calendar_Cat1'[Result]),
    FORMAT(
		CALCULATE(
			COUNTROWS('Table_by_calendar_Cat1'),
			'Table_by_calendar_Cat1'[Result] = "Yes"
		) + 0, 
	"0")
)

 To get around this, if its a problem, we can add some additional checks to divert the outcome to blank() in the contexts where you are getting 0 whenever you don't want it.

[DEEW · Accepted solution]

OMG, Perfect , it worked a treat,

Thank you so much Ross. 

[RossEdwards]

I would create a measure that checks if its running under a single answer context and if it is, provides the answer.  If its not, it does a count of the records within the multianswer context that equal yes.  Here is a quite example bit of code that you'll need to edit and test.

Your Measure = IF(
    COUNTROWS('YourTable') = 1,
    SELECTEDVALUE('YourTable'[YourField]),
    FORMAT(CALCULATE(
        COUNTROWS('YourTable'),
        'YourTable'[YourField] = "Yes"
    ), "0")
)

[DEEW]

Thanks Ross, However m the Total per each employee is showing 0, it does not ad up the number of yes.. Appreciate any help

[RossEdwards]

First lets check that this measure has been applied correctly.  I'm assuming:

• You are using a matrix visual
• Rows are Employees column
• Columns are your Days Column
• You have the matrix's total column on.
• This measure is only used once in the "Value" part of the matrix configuration allowing the measure to handle the "total" and the individual cross sections.