Output Status as Column

[Anonymous]

Hi All,

In the below table for the first week I worked 40 hours out of 40 so it is billable. Second week same as first week. So for 1st and 2nd week i am Billable.

But for third week i worked only 30 hours out of 40 so i am PB(Partially Billable).

Now in the total rows i want to show CStatus as PB because 110 hours out of 120. In another words when my name is selected without selecting any date it need to show count as 1 for Partially Billable.

Thanks in advance.

[hthota]

Hi 

Create a column as given below.

Column 2 = IF(Table1[Column]="Partially Billable",1,0)

And Place it in a card. Change the Sum.

Thanks,

Hemanth

[Anonymous]

Hi Thanks for your respose.

I tried your suggestion but its not working.

Let me re iterate the thing.

In the below screenshot

if i select may 16 in date then status should show as Billable i.e.,Billable count = 1 and partially Billable =0

if i select may 9 in date then status should show as PB i.e.,Billable count = 0 and partially Billable =1

if i select may 23 in date then status should show as PB i.e.,Billable count = 0 and partially Billable =1

if i select may 16 and may 9 both in date then  it is 70 hours out of 80 then status should show as partially Billable i.e.,Billable count = 0 and partially Billable =1

 

Thanks in Advance

[v-lili6-msft]

HI, @Anonymous 

You could just create a measure instead of [Cstatus] column.

Measure =
IF (
    DIVIDE (
        CALCULATE ( SUM ( 'Table'[Billing Hours] ) ),
        CALCULATE ( SUM ( 'Table'[HoursForWeek] ) )
    ) < 1,
    "PB",
    "Billable"
)

Result:

here is pbix file, please try it.

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft ,

Thanks for your respose. It is working as expected but i want that as column because I would like to see that in Pie Chart.

Regards,

Prakash

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You may try this way:

Step1:

Create a Cstatus Dim table(Do not create a relationship with basic table).

Step2:

Then use this formula add a new measure

Measure 2 = IF(SELECTEDVALUE(Dim[Cstatus])=[Measure],COUNTA('Table'[Name]))

Step3:

Drag Cstatus field from Dim table and measure 2 into a pie chart.

Result:

here is new sample pbix file, please try it.

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft ,

It is working Fine.

Just made a small change in Measure 2. The requirement was Distinct count of Table Name.

Updated Measure 2

Measure 2 = IF(SELECTEDVALUE(Dim[Cstatus])=[Measure],CALCULATE(DISTINCTCOUNT('Table'[Name])))

Thanks for your help.

Regards,

Prakash

[Anonymous]

Hi,

@v-lili6-msft 

when i filter 2 names at a time it is not working.

In the below screenshot i have selected 2 resource and i expected 1 PB and 1 Billable in Pie chart but it is not working as expected.

[Anonymous]

Hi @v-lili6-msft ,

Just rephrasing the below content.

I selected May 23 alone in date and i am expecting Prakash as Partially Billable and Nethaji as Billable.

So pie chart should display as 1 - 1 i.e., 50%.

Let me know whether we can achieve this data or not.

Regards,

Prakash

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

If so, try these two measure:

Measure = 
CALCULATE(IF (
    DIVIDE (
        CALCULATE ( SUM ( 'Table'[Billing Hours] ) ),
        CALCULATE ( SUM ( 'Table'[HoursForWeek] ) )
    ) < 1,
    "PB",
    "Billable"
),FILTER(ALLSELECTED('Table'),'Table'[Name]=SELECTEDVALUE('Table'[Name])))

Measure 2 = VAR _table=SUMMARIZE('Table','Table'[Name],"_discount",[Measure]) 
var _table2=FILTER(GENERATE(_table,Dim),[Cstatus]=[_discount]) return
COUNTAX(_table2,[Name])

If you still have the problem, please share your sample pbix file.

Best Regards,

Lin