cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Output Status as Column

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.

 

Screenshot_1.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Output Status as Column

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:

1.JPG

 

2.JPG

here is new sample pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Community Support Team
Community Support Team

Re: Output Status as Column

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

14 REPLIES 14
hthota Member
Member

Re: Output Status as Column

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
Not applicable

Re: Output Status as Column

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

 Screenshot_3.png

Thanks in Advance

Community Support Team
Community Support Team

Re: Output Status as Column

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:

8.JPG9.JPG

 

here is pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Output Status as Column

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.

Screenshot_4.png

 

Regards,

Prakash

Community Support Team
Community Support Team

Re: Output Status as Column

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:

1.JPG

 

2.JPG

here is new sample pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Re: Output Status as Column

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
Not applicable

Re: Output Status as Column

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.

 

Screenshot_5.png

Highlighted
Anonymous
Not applicable

Re: Output Status as Column

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

Community Support Team
Community Support Team

Re: Output Status as Column

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,399)