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
Hi There,
I'm using matrix visual in my power bi report where column total is coming as expected but row total is not calculating as expected.
This is the fields I used in Matrix visual
| Matrix Visual |
| Row = ID , ProductName |
| Column = Calendar [Year-Month] |
| Values = Unit Measure |
Note: Caledar table created and year-month column from this table to year-month of main table is connected where filter direction flows from calendar to main table
Date column from calendar table is used in date slicer and its selected as caledar format (start and end date can be seelcted using calendar format).
Measure Logic:
| Start date | 02-01-2023 |
| End Date | 03-06-2023 |
We need to display 6 Months in Matrix column as Jan-2023, Feb-2023 ... June-2023 and the below meausre value should be displayed under each month and in Martix visual total we need to display column subtotals.
Logic for first month : If (currentdayof startmonth = 1, sum(units), units / totaldaysinstartmonth * (totaldaysinstartmonth -currentdayof startmonth))
Logic for Last month : sum(units) / (totaldaysinstartmonth * currentdayof endmonth)
Middle months = Sum(units)
Expected result: Assume Total units =500 for all the months for example.
| Measure Logic | ||||||||
| Month | 1 | 2 | 3 | 4 | 5 | 6 | ||
| Units | 500 | 500 | 500 | 500 | 500 | 500 | ||
| Number of days | 31 | 28 | 31 | 30 | 31 | 30 | ||
| Selection day | 2 | 3 | ||||||
| Result | 467.74 | 500 | 500 | 500 | 500 | 50.00 | ||
| Total | 467.74 | 500 | 500 | 500 | 500 | 50 |
THis is the simple example from excel.
but in power bi this will be displayed in Matrix visual like below:
In Power BI, row total is not calculating properly and also I would like to know is it possible to apply logic for first month and last month different calc and in middlemonths to display the direct vlaue using matrix visual ? BElow is the measue I tried which just applies logic for starting month itself and using this am facing row total issue. Could you pls. assist on solving the both?
Units_In_Month = VAr First = EOMONTH([StartDate],-1)+1
VAR DaysInMonth = DAY(EOMONTH([StartDate],0)) -- Number of days in Start Date
VAR CurrentDay = DAY([StartDate]) -- Current day (10)
VAR difference =ABS(DaysInMonth - CurrentDay)
VAR Units = SUM('Master-Data'[UNIT])
VAR TotalUnits = IF(CurrentDay =1,
Units,
Units/DaysInMonth*difference)
RETURN IF(ISINSCOPE('Calendar'[Year-Month]),TotalUnits,CALCULATE(SUMX('Master-Data','Master-DAta'[unit])))
Hi Yes, Initial was marked as spam by mistake so I recreated it again.
- AlexisOlson
Super User
Just FYI, putting "Urgent" in the title makes a post far more likely to be marked as spam. There's been a ton of spam posts lately, so occasionally some non-spam posts may get marked accidentally.
Attaching the Original Post Link : Urgent: Inconsistent Row Totals in Power BI Matrix... - Microsoft Fabric Community
- lbendlin
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/1447523Hi 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!
