Urgent: Inconsistent Row Totals in Power BI Matrix Visual with Custom Logic for Start and End Month

[SelviPrabhu]

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])))

[lbendlin · Accepted solution]

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-...

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/1447...

[lbendlin · Accepted solution]

Materialize your measure.

[SelviPrabhu]

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])))

[SelviPrabhu]

Hi Yes, Initial was marked as spam by mistake so I recreated it again.

[AlexisOlson]

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.

[SelviPrabhu]

Attaching the Original Post Link : Urgent: Inconsistent Row Totals in Power BI Matrix... - Microsoft Fabric Community

[lbendlin · Accepted solution]

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-...

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/1447...

[SelviPrabhu]

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.

[SelviPrabhu]

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 · Accepted solution]

Materialize your measure.

[SelviPrabhu]

Hi @lbendlin 

As I mentoned earlier the logic is working as expected but Year_Month

column in the matrix table is not dynamically updating based on the slicer date selection. In your first approach the column was dynamically changing but in second approach everything works fine except dynamically displaying the year_month based on slicer selection. seems like in UIM measure we need to adjust the logic to dynamically dispaying the year_month based on selection.

Could you pls, revisit this to troubleshoot the issue and find a resolution? Thanks in Advance!

[SelviPrabhu]

Hi @lbendlin I created one more measure as below and used this in values section of Matrix. now the columns are displaying dynamically based on date selection. Do we have any other approach other than this ? We need to consider performance as well..

Measure = IF(ISINSCOPE('Calendar Table'[Year_Month]),[Units_in_month],[UIM])

[lbendlin]

Not clear to me what your new question is. Maybe create a separate thread?  

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-...

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/1447...

[SelviPrabhu]

Hey Hi @lbendlin My bad, It seems like I mistakenly tried modifying the logic. So year-months columns was not changing based on date slicer selection and to solve that I was trying out different apporaches using Isinscope functions. But now tried with your second approach without altering the code, I can see the expected result. Now all set, sorry for the confusions!

[SelviPrabhu]

Hi @lbendlin Pls. refer to the image below: 

The logic for starting month is not working as expected. For remaining monthis its working fine.

Example: selected dates = 2/6/2023 to 1/7/2023

2023-06 Total unit is 1 and for 1/7/2023 also Total unit is 1.

So now for the first month 2023-06 the expected output is 0.94 but this measure gives the result as 0.97 but for last month (1/7/2023) its giving the correct result.

As per my analysis for first month the logic should work as (Totalydaysinfirstmonth-selecteddayinfirstmonth) * totaldaysincalendartable. But the measure used in image calcualtes 30/31 instead of 29/31 for the june month. Could you pls check on the logic for first month alone keeping the rest of the month logics as same. Return same unit for middle of the months and for last month the logic should be Totalunits *(totaldaysinmonth -selectedday)

[SelviPrabhu · Accepted solution]

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 Date

VAR CurrentDay  = DAY(StartDate)     -- Current day

VAR 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 

[SelviPrabhu]

Hi @lbendlin  Did you get chance to check on this one?

[SelviPrabhu]

Thanks @lbendlin Yes this works! Thanks again for putting effort on solving this!

[parry2k]

@SelviPrabhu seems like a duplicate post.

[SelviPrabhu]

Thanks @parry2k  Hello @Greg_Deckler  Could you pls. check on this and confirm? I tried with many approaches but its not matching my requirement and facing issues in row total of each row. I tried with summarize, allexcept, all but row total issue not solving & also I would like to implement the logic as mentioned above for first and last month using the single measue which will be used in values section of matrix visual.

[parry2k]

@SelviPrabhu @Greg_Deckler  can give you a hand on this.