Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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] ) )
Please vote for this problem so it can be resolved:
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] ) )
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.
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!
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] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |