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...
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 
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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)

 

Anonymous's avatar
Anonymous
Not applicable
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