Grand Total of measure doesn't equal sum of row values

[elpulley]

I have daily data and am trying to calculate minimum values over a certain time period. The value for each row is correct and shows the minimum value for that floor over the time period. The Grand Total value of 232 is not the sum of the minimums (213), but is the minimum single day total over the time period. 

 

The measure code is: Office Worker Swipes - Min = MINX(DateTable,DailySwipes[Office Worker Swipes (C)]) where Date Table is my mapping table for each day and Office Worker Swipes (C) is a measure calculating the value for each floor each day. Can someone help me with my syntax so that the values in the rows stay the same but the grand total calculates the sum of the rows?

[kliberty]

Please vote for this problem so it can be resolved:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17298271-matrix-totals-only-as-su...

[Vvelarde · Accepted solution]

@elpulley

Hi, use this as guide to obtain your total, Specially the bold part

MinOW =
IF (
    HASONEVALUE ( Table1[Floor] ),
    MINX ( DateTable; MIN ( Table1[Office Workers Swipe] ) ),
    SUMX (
        SUMMARIZE (
            Table1;
            Table1[Floor],
            "MINVALUE"; MIN ( Table1[Office Workers Swipe] )
        );
        [MINVALUE]
    )
)

[elpulley]

Can you help me understand what this formula is doing? I am getting a huge value. Also, my Office Workers Swipe (C) column is a measure, so I cannot use it in a MIN function.

[Vvelarde]

@elpulley

Can you share me some sample data and the formula of your measure.

Thanks

[elpulley]

I am not sure what your formula is doing, but I was able to make it work. I had to create a Custom Column in the query editor to make the Office Workers Swipes a column in the data rather than a measure summing two columns. This allowed me to use it in the MIN function in the forumla above. The picture below shows a week of sample data swipes, my old min column with an incorrect Grand Total, and your new min column with the correct grand total:

My measure code is below: 

MinOW = IF(
			HASONEVALUE(LocationMaster[Floor]),
			MINX('Date Table',MIN(DailySwipes[Office Worker Swipes])),
			SUMX(
				SUMMARIZE(
					LocationMaster,
					LocationMaster[Floor],
					"MINVALUE",MIN(DailySwipes[Office Worker Swipes])
				),
			[MINVALUE]
			)

Thanks for your help!

[Vvelarde]

@elpulley

The Formula works in this way:

MinOW = 
//First evaluate if is in Floor Rows or is in the Total (When HASONEVALUE is False is the total)

IF(
			HASONEVALUE(LocationMaster[Floor]),

//if is true obtain the MINX of your Office Worker Swipes
			MINX('Date Table',MIN(DailySwipes[Office Worker Swipes])),

//if is false create a summarize table when the floor and his respective MINX Office Worker Swipes and
after that aaplied a SUM of all the MINVALUEs.
			SUMX(
				SUMMARIZE(
					LocationMaster,
					LocationMaster[Floor],
					"MINVALUE",MIN(DailySwipes[Office Worker Swipes])
				),
			[MINVALUE]
			)