Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

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 date02-01-2023
End Date03-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        
 Month123456 
 Units500500500500500500 
 Number of days312831303130 
         
 Selection day2    3 
         
 Result467.7450050050050050.00 
 Total467.7450050050050050 

 

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

Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    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/1447523

  • Anonymous's avatar
    Anonymous
    1 year ago

    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 

20 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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 date02-01-2023
    End Date03-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        
     Month123456 
     Units500500500500500500 
     Number of days312831303130 
             
     Selection day2    3 
             
     Result467.7450050050050050.00 
     Total467.7450050050050050 

     

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

    • parry2k's avatar
      parry2k
      Super User
      1 year ago

      Anonymous seems like a duplicate post.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

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

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        1 year ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.