Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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

15 REPLIES 15
hthota
Resolver III
Resolver III

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

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

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

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

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

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

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

Anonymous
Not applicable

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

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.

Dear Power Bi Community,

 

Greetings to all and hope you are safe during this pandemic!

I'm new to this community and I have just completed the registration.

So to start with, I'm unable to produce a simple calculation in power bi, which is way easier to calculate in excel i.e., a specific field rate within the column range.

 

So here is the image of what I need, the left side is the raw table in Power Bi, the summary is in the centre table for illustration and the result needed is in the right side i.e., Closure % (closed divided by total cases of that account). 
Sorry if I'm asking a too-simple question since I'm new to Power BI and still on self-learning 🙂Sample BI.JPG


Appreciate your kind assistance in this matter.

 

Regards,

Musa

Anonymous
Not applicable

Hi  @v-lili6-msft ,

 

Based on your suggesstion, i achieved the scenario but

i have the problem in association. 

if i click the  chart1, chart2 could not be associated.

https://avacorp1-my.sharepoint.com/:u:/g/personal/yuvaraj_g_avasoft_com/EW8bYSSWUDJGjozdo_fWWKUBI-Ql...

 

Any help would be appreciated

 

Thanks in advance

Prakash

Anonymous
Not applicable

Hi @v-lili6-msft ,

 

Just rephrasing the below content.

 

Earlier we created "Billing Status" Pie chart based on "Billing Hours" column.

Now i have created "Utilization Status" Pie Chart based on "Utilization hours" column. (Similar technique followed  i.e., same as Billing Status pie chart )

 

Hours.png

Now the problem when i select any of these Pie chart then the oter Pie chart is not getting associated based on selection.

 

data model.png

Provided the Power BI File.

https://avacorp1-my.sharepoint.com/:u:/g/personal/prakash_m_avasoft_com/EQOZ4KYXB29KqZxT2JuhsyQBN0vi...

 

Regards,

Prakash

 

hi, @Anonymous 

There are two mistakes in your report:

1. For Dim1 Utilizationstatus, It is "Partiallyutilized" and "Fullyutilized", but in your formula is "Partially utilized" and "Fully utilized", they couldn't match.

1.JPG2.JPG

 

2. In UtilisationOutput measure, " Dim1=[Utilizationstatus] " is wrong.

It should be like this: 

[Utilizationstatus]=[_utilize]
 

3.JPG

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

Hi 

 

 

 

 

Prvided the Pbix file.

https://avacorp1-my.sharepoint.com/:u:/g/personal/yuvaraj_g_avasoft_com/EVTM05iN7CJCp7MW1SNygu0BkOtqEDUbbnedNN_IGk7leQ?e=rsKFHh





 
 
 
 
 
 
Anonymous
Not applicable

Hi @v-lili6-msft ,

 

It is working fine now thanks.

 

Regards,

Prakash

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.