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,
So we are changing to a unique dashboard instead of creating multiple excel files for each week in various reports, I'm having a problem with one of them.
It's a Capacity Utilization based on Billable and non Billable Hours, one of the colums where I get the data has 2 text values "no" and "yes", so I've created two pivot table, one with the total counting both but showing 2 row for each employee and the "yes" and "no" options with the hours for each one, the colums is year and month, and another Pivot Table where it shows only the billable ones.
With that I have a Colum with Line chart for each employee where Colum is the total utilization (sum of yes and no) and billables and the line is the % of each. -- Here is my problem, because I see that Power Bi doesn't read a specific row to create a chart, but colums, so in the excel I just use the "=" to the pivot sheet with the results for each employee for each month but I can't do that on PowerBi, and if I just import this data for PBI it won't read since it's a bunch of rows and not colums, I was thinking about creating 4 more colums, one with the "sum if yes" one "sum" for everything "% of sum yes" and "% sum".
If anyone wants to help I can send over the excel with the pivots and etctera.
https://imgur.com/a/LXkcE3i - The problematic one
Regards,
Solved! Go to Solution.
Hi @GeorgesKV,
Please be aware of your data safety.
If I understand your requirements correctly, it could be done by four measures as follows. Please also check out the demo in the attachment.
sumOfYes = CALCULATE ( COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ), PM__Time_Detail_by_Month_by_Bra[Billable] = "Yes" )
sumOfNo = CALCULATE ( COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ), PM__Time_Detail_by_Month_by_Bra[Billable] = "No" )
%sumOfYes = DIVIDE ( CALCULATE ( COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ), PM__Time_Detail_by_Month_by_Bra[Billable] = "Yes" ), CALCULATE ( COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ), ALL ( PM__Time_Detail_by_Month_by_Bra[Billable] ) ), 0 )
%sumOfNo = DIVIDE ( CALCULATE ( COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ), PM__Time_Detail_by_Month_by_Bra[Billable] = "No" ), CALCULATE ( COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ), ALL ( PM__Time_Detail_by_Month_by_Bra[Billable] ) ), 0 )
Best Regards,
Dale
Hi @GeorgesKV,
The link is invalid. It's better to share a sample file. Then we can test and build solutions.
I think you need several measures in your scenario. Then put it in a table visual, the result will be aggregated by context. Please provide a sample file.
Measure = count('table'[column])
Best Regards,
Dale
Did my sample help?
Hi @GeorgesKV,
Please be aware of your data safety.
If I understand your requirements correctly, it could be done by four measures as follows. Please also check out the demo in the attachment.
sumOfYes = CALCULATE ( COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ), PM__Time_Detail_by_Month_by_Bra[Billable] = "Yes" )
sumOfNo = CALCULATE ( COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ), PM__Time_Detail_by_Month_by_Bra[Billable] = "No" )
%sumOfYes = DIVIDE ( CALCULATE ( COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ), PM__Time_Detail_by_Month_by_Bra[Billable] = "Yes" ), CALCULATE ( COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ), ALL ( PM__Time_Detail_by_Month_by_Bra[Billable] ) ), 0 )
%sumOfNo = DIVIDE ( CALCULATE ( COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ), PM__Time_Detail_by_Month_by_Bra[Billable] = "No" ), CALCULATE ( COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ), ALL ( PM__Time_Detail_by_Month_by_Bra[Billable] ) ), 0 )
Best Regards,
Dale
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |