Calculations that account for zero entries and roll up the hierarchy.
My locations have a goal of 1 inspection per day. I’m report on a rolling 7 days. How do I calculate the percentage complete where the percentage doesn’t get inflated when a location completes more than 1 on any day but doesn’t complete any on other days? Here is my example. Location #530 completed a total of 7 inspections. However, they only completed them 5 out of 7 days. Calculation of # of inspections divided by goal of 7 would show them at 100% compliant. But they really are not because the goal is 1 per day, and they have 2 days with zero completed. So they should show about 71%. I also need this calculation to rollup to the District (07) and the Division (035). Also, need to cap the percentage at 100%. This is how I would do it in Excel: I can not figure out how to duplicate the Percent Complete in Power BI and it properly roll up to District and then Division. Here is my count of days with >0 inspections per day. You can see because I’m using DistinctCount, it is not rolling up to the District and Division properly. Once the count of stores and days with inspections is configured, I will also have to calculate the percentage completed. In the example above, it is returning nothing.
Filtering by histogram bins in Power BI
Hi Everyone, In Power BI I have created a histogram that shows Number of People per Attendance Rate. To do so I have created additional table that defines the ranges for the histogram, which looks like this: HistogramBins: IDBinMaxMin 1 0-20% 0.2 0 2 20-40% 0.4 0.2 3 40-60% 0.6 0.4 4 60-80% 0.8 0.6 5 80-100% 1.01 0.8 Then I created the measure that would count how many people falls into each range by Attendance rate: Number of people per range = VAR minbin = SELECTEDVALUE('Histogrambins'[Min]) VAR maxbin = SELECTEDVALUE('Histogrambins'[Max]) VAR segment = FILTER( ADDCOLUMNS( SUMMARIZE('People', 'People'[ID]), "@Attendance", [% Attendance rate]), [@Attendance] < maxbin && [@Attendance] >= minbin) RETURN CALCULATE([Number of People], segment) It works nice for the Histogram itself, but I would like to add the option to click on Histogram bins and to have the rest of the report filtered for People[ID] that fall into each range. In the report I have slicer with Period (which is selected to show last 6 months) and an overview of Attendance rate per month which uses the measure % Attendance rate: % Attendance rate = DIVIDE ([Number of People]), [Number of Invitees]). It works when I am not filtering by histogram bins. I have tried to use CONCATENATEX list of People ID and then to filter the meausre by CONTAINSSTRING using People ID from the People table that is connected to People ID in the Attendance table, but when I click on specific bin, in the overview by month, it omits the months when Attendance rate falls below selected range per this month. I would like to separate somehow the inital range of Attendance rate from the second calculation of the Attendance rate so when I, for example, click on range 80-100% I would see the overview for people that fall into this range for selected period, even if for some months in the selected period, it falls below 80%. Would appreciate any help or hint.Ignore a slicer but consider another on the same page
I have a table called Order, with columns Order, Reported date, Finish Date etc.I want to create a dax mesure to calculate Total no of workorders in that table such that the measure when pulled in a visual to show total orders should change with Reported date slicer but it should not affected by a Finish Date slicer on the same page. For example slicer on Finish date shows date from 1/29/2024 to 1/31/2024 and total orders created as 103 which is actually for any reported date on or after 1/29/2024, now when date on slicer for Finish Date is chnaged to 1/30/2024-1/31/2024 it should still show 103 and same 103 for finish date 1/29/2024 to 1/31/2024 I need to have another measure in the same visual which calculates orders which are closed and completed and this measure should be affected by Finish date slicer and not the reported date slicer
Power BI Dax to get Most recent value from child table
Hi , I am new to Power BI and need help in developing a DAX formula to generate a report. I have 2 tables. Table 1 : Table 2 : Req ID is a foreign key from Table 1 to Table 2, I need to get the most recent comment from table 2 based on table 1 req ID and show as a table format.
Summarize table Lookupvalue in Funnel Visual
Hi Everyone, i have the below summarized table "Cover Type Analysis" as per the below : CoverTypeAnalysis = SUMMARIZE('Production ReportV20-V1 Power B','Production ReportV20-V1 Power B'[Cover Type],'Production ReportV20-V1 Power B'[Transaction Year],"Total Premium",SUM('Production ReportV20-V1 Power B'[Cedent Premium])) i have created a new column LastYear: LastYear = IFERROR(FORMAT(CoverTypeAnalysis[Transaction Year],"General Number") - 1,0) i need to calculate PercentageYearlyGrowthByCoverType: PercentageYearlyGrowthByCoverType = DIVIDE(CoverTypeAnalysis[Total Premium] - LOOKUPVALUE(CoverTypeAnalysis[Total Premium],CoverTypeAnalysis[Transaction Year],CoverTypeAnalysis[LastYear], CoverTypeAnalysis[Cover Type],CoverTypeAnalysis[Cover Type]), ABS(LOOKUPVALUE(CoverTypeAnalysis[Total Premium], CoverTypeAnalysis[Transaction Year],CoverTypeAnalysis[LastYear], CoverTypeAnalysis[Cover Type],CoverTypeAnalysis[Cover Type])))/100 i have Transaction Year a Slicer Filter = 2019, everything looks wonderfull and the Funnel is looking correct: I was asked after that to add Quarter and Month Filters to this report, i added them to the summarized table : CoverTypeAnalysis = SUMMARIZE('Production ReportV20-V1 Power B', 'Production ReportV20-V1 Power B'[Cover Type], 'Production ReportV20-V1 Power B'[Transaction Year], 'Production ReportV20-V1 Power B'[Transaction Quarter], 'Production ReportV20-V1 Power B'[Transaction Month], "Total Premium",SUM('Production ReportV20-V1 Power B'[Cedent Premium])) and adjusted PercentageYearlyGrowthByCoverType to be as per the below: PercentageYearlyGrowthByCoverType = DIVIDE(CoverTypeAnalysis[Total Premium] - LOOKUPVALUE(CoverTypeAnalysis[Total Premium], CoverTypeAnalysis[Transaction Year],CoverTypeAnalysis[LastYear], CoverTypeAnalysis[Transaction Quarter],CoverTypeAnalysis[Transaction Quarter], CoverTypeAnalysis[Transaction Month],CoverTypeAnalysis[Transaction Month], CoverTypeAnalysis[Cover Type],CoverTypeAnalysis[Cover Type]), ABS(LOOKUPVALUE(CoverTypeAnalysis[Total Premium], CoverTypeAnalysis[Transaction Year],CoverTypeAnalysis[LastYear], CoverTypeAnalysis[Transaction Quarter],CoverTypeAnalysis[Transaction Quarter], CoverTypeAnalysis[Transaction Month],CoverTypeAnalysis[Transaction Month], CoverTypeAnalysis[Cover Type],CoverTypeAnalysis[Cover Type])))/100 however im getting wrong results as if it excecute the formula for each row and at the it sums all percentages while what i want is to make the calculation for all selected values: also i tried to create a mesure but it returned empty and couldnt load it into Funnel Visual PercentageYearlyGrowthByCoverType mesure = DIVIDE(SUM(CoverTypeAnalysis[Total Premium]) - LOOKUPVALUE(CoverTypeAnalysis[Total Premium], CoverTypeAnalysis[Transaction Year],SELECTEDVALUE(CoverTypeAnalysis[LastYear]), CoverTypeAnalysis[Transaction Quarter],SELECTEDVALUE(CoverTypeAnalysis[Transaction Quarter]), CoverTypeAnalysis[Transaction Month],SELECTEDVALUE(CoverTypeAnalysis[Transaction Month]), CoverTypeAnalysis[Cover Type],SELECTEDVALUE(CoverTypeAnalysis[Cover Type])), ABS(LOOKUPVALUE(CoverTypeAnalysis[Total Premium], CoverTypeAnalysis[Transaction Year],SELECTEDVALUE(CoverTypeAnalysis[LastYear]), CoverTypeAnalysis[Transaction Quarter],SELECTEDVALUE(CoverTypeAnalysis[Transaction Quarter]), CoverTypeAnalysis[Transaction Month],SELECTEDVALUE(CoverTypeAnalysis[Transaction Month]), CoverTypeAnalysis[Cover Type],SELECTEDVALUE(CoverTypeAnalysis[Cover Type]))))/100 appreciate your urgent assistance Thank you in advance Best regards, Georges Sabbagh
when applying filter should not affect dax measure
Hi Experts, I'm searching for solution that when applying filter it should not affect dax. Days To Fulfill = VAR childorderdate =CALCULATE( MAXX(CALCULATETABLE('PBI_BBA_SALES',FILTER('PBI_BBA_SALES',NOT ISBLANK('PBI_BBA_SALES'[Ref Id]) ) ),PBI_BBA_SALES[Order Date]) ) Return DATEDIFF(MAX('PBI_BBA_SALES'[Order Date]),childorderdate,DAY) this is the measure I have created but when I add filter to the table it should not affect the dax this is the solution I need timlCompare two rows and return the one with the earliest date Power Bi
Hi, I have a table called "Jira tickets", in which I have multiple rows for the same ticket, as you can see below in the sample data: If we take for example Ticket 1, how can I compare the rows to take only the first Sprint when this ticket was in status "Done", based on the Status date? In this case, to have a calculated column that will say "include" for Sprint 7 (08-Sep-22) and "exclude" for Sprint 8 (04-Oct-22). I will have to do the same thing for the status "To Do". Thank you very much! 😊
