Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 on...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

hthota's avatar
hthota
Resolver III
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-lili6-msft
        Community Support
        7 years ago

        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