Forum Discussion
Urgent: Inconsistent Row Totals in Power BI Matrix Visual with Custom Logic for Start and End Month
This here is a forum where users help users, time permitting. For urgent requests contact a Microsoft partner near you.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Materialize your measure.
Hi lbendlin I've slightly adjusted the logic and its working fine for me now:
Units_in_month =VAR curr_month= MAX('Calendar Table'[Year_Month])VAR days_in_month_ratio=DIVIDE(CALCULATE(COUNTROWS('Slicer'),Slicer[Year_Month]=curr_month),COUNTROWS('Calendar Table'),0)VAR TotalUnits = SUM('Master-Data'[UNIT])VAR StartDate = MIN('Slicer'[Date])VAR DaysInMonth = DAY(EOMONTH(StartDate,0)) -- Number of days in Start DateVAR CurrentDay = DAY(StartDate) -- Current dayVAR difference = ABS(DaysInMonth - CurrentDay)RETURN IF(MIN('Slicer'[Year_Month])=curr_month && CurrentDay<>1,TotalUnits / DaysInMonth * difference,days_in_month_ratio * TotalUnits)Also, adjusted calendar table logic because if we have multiple years of data and then ,if we choose latest year in the date filter then logic for last month column is not working as expected. Just mentionig this here if in case somebody else searching for the soltuion on it..Calendar Table = CALENDAR(Min('Master-Data'[Date]),EDATE(Max('Master-Data'[Date]),12))Thanks for your co-ordination on acheiving the solution lbendlin
Super UserThis here is a forum where users help users, time permitting. For urgent requests contact a Microsoft partner near you.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi lbendlin I've shared the sample power bi file (refer below link to access the file). Pls. refer the file and help me on acheiving the mentioned scenarios in the post.
First Month Logic of selected start date = if (currentday(startdate)=1, sum(unit),sum(unit)/Totaldaysinmonth*(Totaldaysinstartmonth-currentday(startdate))
Last Month Logic of selected End date =sum(unit)/Totaldaysinendmonth*currentday(enddate)
In between Month logic = Sum(unit).
Along with this row total should display the total based on the sum of the values being displayed in each month . example 0.94+1+1 = 2.94 instead of 2.81 or 3. Hope this helps!
- lbendlin
You need to use disconnected tables to feed your slicer.
Thank You lbendlin Now I can see the calc in first month and last month working as expected in unints_in_months measure. But I'm not seeing the row totals for units_in_months measure. Could you pls. help of fetching the totals for each product wise (row totals).. Example for fridge it should be 0.97+1+0.06 = 2.03.
- lbendlin
Materialize your measure.