Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Hi
Create a column as given below.
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
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
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
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
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
Hi,
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.
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
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 🙂
Appreciate your kind assistance in this matter.
Regards,
Musa
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.
Any help would be appreciated
Thanks in advance
Prakash
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 )
Now the problem when i select any of these Pie chart then the oter Pie chart is not getting associated based on selection.
Provided the Power BI File.
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.
2. In UtilisationOutput measure, " Dim1=[Utilizationstatus] " is wrong.
It should be like this:
Best Regards,
Lin
As per your comments i updated the dax query, still the chart could not be associated.
Prvided the Pbix file.
https://avacorp1-my.sharepoint.com/:u:/g/personal/yuvaraj_g_avasoft_com/EVTM05iN7CJCp7MW1SNygu0BkOtqEDUbbnedNN_IGk7leQ?e=rsKFHh
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |