Forum Discussion

ptmuldoon's avatar
ptmuldoon
Icon for Resolver I rankResolver I
2 years ago

Matrix visual not showing totals with Date Column?

I have two reports built in Desktop that is listing out values for each of our divisions/Hotels.  And this report below that lists each property looks and totals correctly.

 

 

However, when I take the same report/visual, and swap out the hotel column for a date field and using the same measures, the matrix does not total correctly, and appears the totals are showing the last day of month (last row)?

 

Does anyone has any thoughts on that?  These are the same measures in both reports, and one totals and one does not?

 

 

 

 

Need Help

3 Replies

  • Kaviraj11's avatar
    Kaviraj11
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hi,

     

    You can write measure and adjust calculation accordingly as below:

     

    Total Accomodation Fees =
    VAR Total=[Measure1]
    RETURN
    IF(
        ISINSCOPE('DateTable'[Date]) ,
        Total,
        SUMX(
            'Table',
            'Table'[columnusedformeasure] 
        )
    )
  • ptmuldoon's avatar
    ptmuldoon
    Icon for Resolver I rankResolver I
    2 years ago

    Kaviraj11 

     

    Thanks, and I tried modifiying my measure as you mentioned, but then it throws off the other tables that also use those measure with the date field also being used.

     

    Below is the current measure as I have it.  And this totals correctly on a property level by month, but will not provide the totals when I switch to list it daily.    And it does appear related to the measure computing the number of days. If i take out the DATESINPERIOD, I will get a correct total on when listing by day, but then lose the functionality of switching the meaure based on period selection.

     

    I may just need to remove the totals for now till I learn a better approach

    Comp Rooms = 

    Var Myitem = "Comp Rooms"
    
    Var MyPeriod = [Period Day Selected]
    RETURN

        SWITCH (
            VALUES ('FactType'[FactType]),
                "#", CALCULATE(
                    [Qty]
                    , 'Dim Item','Dim Item'[description]=Myitem
                    ,  DATESINPERIOD ( 'Dim Period'[Date], MAX ( 'Dim Period'[Date] ), -VALUE(MyPeriod), DAY) // this works
                ),
                    
                "% of Avail. Rooms", DIVIDE( 
                    CALCULATE([Qty], 'Dim Item','Dim Item'[description]=Myitem,  DATESINPERIOD ( 'Dim Period'[Date], MAX ( 'Dim Period'[Date] ), -VALUE(MyPeriod), DAY)),
                    CALCULATE([Qty], 'Dim Item','Dim Item'[description]="AVAILABLE ROOMS",  DATESINPERIOD ( 'Dim Period'[Date], MAX ( 'Dim Period'[Date] ), -VALUE(MyPeriod), DAY))    
                )      
	)
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Kaviraj11 ,thanks for the quick reply, I'll add more.

    Hi ptmuldoon ,

    If you need to switch the time granularity between 'month' or 'day', you can first create a field parameter.

    Then create two measures, one for calculating monthly data and one for calculating daily data. Create two virtual tables to store the results of the measures. Then set some judgment conditions, select different measures based on the fields in the matrix, and finally add up all the values ​​in the virtual tables in the total row.

    Something like this 

    Measure = 
VAR _Y_axis = VALUES(Parameter[Parameter Order])  
VAR _table1 = SUMMARIZE('Table',[Year],"Result",[yourmeasureForYear])
VAR _table2 = SUMMARIZE('Table',[Month],"Result",[yourmeasureForMonth])

RETURN 
IF(_Y_axis = 0,
   IF( ISBLANK(SELECTEDVALUE('Table'[Year])),SUMX(_table1,[Result]),[yourmeasureForYear]),
    IF( ISBLANK(SELECTEDVALUE('Table'[Month])),SUMX(_table2,[Result]),[yourmeasureForMonth])
)

     

    Best Regards,
    Wenbin Zhou