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.
UPDATE: 26 May - Unfortunately still unable to get it working. I've taken the time to create dummy data so it reflects the actual code as much as possible.
Desired Outcome
Based on dates selected in slicer:
Sample Data
SlicerGroup1 | Date | SlicerGroup2 | ID | Value | Group | Sub_Group | Sub_Sub_Group | Sub_Sub_Sub_Group |
SG1_1 | 31-Mar-21 | SG2_1 | ID_1 | -0.03% | Group_1 | G1_SG1 | G1_SG1_SSG1 | G1_SG1_SSG1_SSSG1 |
SG1_1 | 31-Mar-21 | SG2_1 | ID_2 | 0.02% | Group_1 | G1_SG1 | G1_SG1_SSG1 | G1_SG1_SSG1_SSSG2 |
SG1_1 | 31-Mar-21 | SG2_1 | ID_3 | -0.04% | Group_1 | G1_SG2 | G1_SG2_SSG1 | G1_SG2_SSG1_SSSG1 |
SG1_1 | 30-Apr-21 | SG2_1 | ID_3 | 0.02% | Group_1 | G1_SG2 | G1_SG2_SSG1 | G1_SG2_SSG1_SSSG1 |
SG1_1 | 30-Apr-21 | SG2_1 | ID_1 | -0.06% | Group_1 | G1_SG1 | G1_SG1_SSG1 | G1_SG1_SSG1_SSSG1 |
SG1_1 | 30-Apr-21 | SG2_1 | ID_2 | 0.04% | Group_1 | G1_SG1 | G1_SG1_SSG1 | G1_SG1_SSG1_SSSG2 |
SG1_2 | 31-Mar-21 | SG2_1 | ID_4 | 0.06% | Group_2 | G2_SG1 | G2_SG1_SSG1 | G2_SG1_SSG1_SSSG1 |
SG1_1 | 4-May-21 | SG2_1 | ID_2 | 0.04% | Group_1 | G1_SG1 | G1_SG1_SSG1 | G1_SG1_SSG1_SSSG2 |
SG1_1 | 3-May-21 | SG2_1 | ID_2 | 0.02% | Group_1 | G1_SG1 | G1_SG1_SSG1 | G1_SG1_SSG1_SSSG2 |
SG1_1 | 4-May-21 | SG2_1 | ID_1 | 0.07% | Group_1 | G1_SG1 | G1_SG1_SSG1 | G1_SG1_SSG1_SSSG1 |
SG1_1 | 3-May-21 | SG2_1 | ID_1 | -0.05% | Group_1 | G1_SG1 | G1_SG1_SSG1 | G1_SG1_SSG1_SSSG1 |
SG1_1 | 4-May-21 | SG2_1 | ID_3 | -0.02% | Group_1 | G1_SG2 | G1_SG2_SSG1 | G1_SG2_SSG1_SSSG1 |
SG1_1 | 3-May-21 | SG2_1 | ID_3 | 0.03% | Group_1 | G1_SG2 | G1_SG2_SSG1 | G1_SG2_SSG1_SSSG1 |
SG1_2 | 30-Apr-21 | SG2_1 | ID_4 | 0.02% | Group_2 | G2_SG1 | G2_SG1_SSG1 | G2_SG1_SSG1_SSSG1 |
SG1_2 | 31-Mar-21 | SG2_1 | ID_1 | 0.04% | Group_1 | G1_SG1 | G1_SG1_SSG1 | G1_SG1_SSG1_SSSG1 |
SG1_2 | 31-Mar-21 | SG2_1 | ID_3 | 0.04% | Group_1 | G1_SG2 | G1_SG2_SSG1 | G1_SG2_SSG1_SSSG1 |
SG1_2 | 31-Mar-21 | SG2_1 | ID_2 | 0.00% | Group_1 | G1_SG1 | G1_SG1_SSG1 | G1_SG1_SSG1_SSSG2 |
SG1_2 | 30-Apr-21 | SG2_1 | ID_3 | 0.04% | Group_1 | G1_SG2 | G1_SG2_SSG1 | G1_SG2_SSG1_SSSG1 |
SG1_2 | 30-Apr-21 | SG2_1 | ID_1 | 0.08% | Group_1 | G1_SG1 | G1_SG1_SSG1 | G1_SG1_SSG1_SSSG1 |
SG1_2 | 30-Apr-21 | SG2_1 | ID_2 | -0.06% | Group_1 | G1_SG1 | G1_SG1_SSG1 | G1_SG1_SSG1_SSSG2 |
SG1_2 | 3-May-21 | SG2_1 | ID_2 | -0.07% | Group_1 | G1_SG1 | G1_SG1_SSG1 | G1_SG1_SSG1_SSSG2 |
SG1_2 | 4-May-21 | SG2_1 | ID_2 | 0.04% | Group_1 | G1_SG1 | G1_SG1_SSG1 | G1_SG1_SSG1_SSSG2 |
SG1_2 | 4-May-21 | SG2_1 | ID_1 | -0.07% | Group_1 | G1_SG1 | G1_SG1_SSG1 | G1_SG1_SSG1_SSSG1 |
SG1_2 | 3-May-21 | SG2_1 | ID_1 | 0.01% | Group_1 | G1_SG1 | G1_SG1_SSG1 | G1_SG1_SSG1_SSSG1 |
SG1_2 | 4-May-21 | SG2_1 | ID_3 | 0.01% | Group_1 | G1_SG2 | G1_SG2_SSG1 | G1_SG2_SSG1_SSSG1 |
SG1_2 | 3-May-21 | SG2_1 | ID_3 | 0.02% | Group_1 | G1_SG2 | G1_SG2_SSG1 | G1_SG2_SSG1_SSSG1 |
SG1_2 | 4-May-21 | SG2_1 | ID_4 | 0.03% | Group_2 | G2_SG1 | G2_SG1_SSG1 | G2_SG1_SSG1_SSSG1 |
SG1_2 | 3-May-21 | SG2_1 | ID_4 | -0.02% | Group_2 | G2_SG1 | G2_SG1_SSG1 | G2_SG1_SSG1_SSSG1 |
Current Output
The current output of interest is shown in the left table of the following image. The right table shows an example of the aggregated values, so if the [Date] is selected as 30-Apr-21, it should be taking in values -0.10% and 0.01%, and returning the minimum, i.e. -0.10% in this case. Based on the measure [2_month_min_datestring], it seems to suggest that the appropriate filters have been applied. Codes for measures are pasted below as well.
2_month_min_datestring =
VAR endDate = LASTDATE(Table2[Date])
VAR startDate = DATE( YEAR(endDate) - 1, MONTH(endDate), DAY(endDate) )
VAR filteredTable =
FILTER (
ALLEXCEPT ( Table2, Table2[SlicerGroup1], Table2[Group] ),
Table2[Date] >= startDate &&
Table2[Date] <= endDate
)
RETURN
"FROM " & MINX( filteredTable, Table2[Date] ) & " TO " & MAXX ( filteredTable, Table2[Date] )
2_month_min_value =
VAR endDate = LASTDATE(Table2[Date])
VAR startDate = DATE( YEAR(endDate) - 1, MONTH(endDate), DAY(endDate) )
VAR filteredTable =
FILTER (
ALLEXCEPT ( Table2, Table2[SlicerGroup1], Table2[Group] ),
Table2[Date] >= startDate &&
Table2[Date] <= endDate
)
RETURN
MINX ( filteredTable, SUM( Table2[Value] ) )
Appreciate any guidance, many thanks - I've been spending many hours on this, but my initial measures still got me the closest.
---
ORIGINAL MESSAGE:
Hi, I've a visualization that currently takes the end date from a slicer on the page. I would like the measure to:
I currently have a measure that feels like it is almost there:
1Y_min =
VAR endDate = LASTDATE(Table[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate) - 1, MONTH(endDate), DAY(endDate) ) -- 1-year lookback
VAR filteredTable =
FILTER (
ALLEXCEPT ( Table, Table[ParentGroup], Table[Group1] ), -- Create a new table, ignoring the Date slicer filter
Table[Date] >= startDate &&
Table[Date] <= endDate
)
VAR sumQuantity =
SUM ( Table[Quantity] )
RETURN
MINX ( filteredTable, sumQuantity )
I do know for a fact that the filtered table is returning the correct date ranges by looking at the results returned by the following code:
1Y_min =
VAR endDate = LASTDATE(Table[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate) - 1, MONTH(endDate), DAY(endDate) ) -- 1-year lookback
VAR filteredTable =
FILTER (
ALLEXCEPT ( Table, Table[ParentGroup], Table[Group1] ),
Table[Date] >= startDate &&
Table[Date] <= endDate
)
RETURN
"FROM " & MINX( filteredTable, Table[Date] ) & " TO " & MAXX ( filteredTable, Table[Date] )
However it seems to fall apart the moment I try to get the MIN/MAX ranges - it is clearly only returning the SUM for the date specified in the slicer (since MIN/MAX returns the same results).
Would appreciate any help, thanks.
Solved! Go to Solution.
Hi @Vera_33 ,
Looking at the sample data I provided again it seems like it may be different from the one I'm using - apologies for that. I was using the RAND() generator in excel and must've accidentally refreshed it. In any case, I managed to build on what you provided and finally got it working. Here's the following code:
2m_min_vera =
VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate), MONTH(endDate)-2, DAY(endDate) ) -- 2-month lookback
VAR slicerSelection = VALUES( Table2[SlicerGroup1] )
VAR filteredTable =
FILTER (
ALLEXCEPT( Table2, Table2[Group] ), -- Create a new table, keeping the Group row context
Table2[SlicerGroup1] IN slicerSelection &&
Table2[Date] >= startDate &&
Table2[Date] <= endDate
)
VAR t1 =
SUMMARIZE(filteredTable, Table2[Date], "sumQuantity", SUM ( [Value] ))
RETURN
-- CONCATENATEX( t1, Table2[Group], "," ) Used for debugging
-- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) Used for debugging
FORMAT( MINX ( t1, [sumQuantity]), "0.00%" )
It seems like using ALLEXCEPT( Table2, Table2[SlicerGroup1]) was somehow not taking into account the slicer selection, since the debugging formulas indicated that slicergroup1 SG1_2 was being included in the filtered table as well.
I also swapped out the GROUPBY with SUMMARIZE mid-way, though they seem to serve the same function.
Thank you so much.
Hi, I'm trying to make use of the bullet charts add-on, and would like to create a few measures, detailed below.
To start with, some sample data:
Date | Grouping 1 | Item ID | Sector | FilterGroup | Quantity |
29-Jan-99 | A | 12431232 | ZZ | X | -100 |
26-Feb-99 | A | 12312333 | ZC | Y | 10 |
26-Feb-99 | A | 12334123 | ZA | X | -10 |
26-Feb-99 | A | 12342332 | ZB | X | -20 |
31-Mar-99 | A | 12321231 | ZA | X | 50 |
29-Jan-99 | B | 16453453 | ZA | Y | 100 |
26-Feb-99 | B | 13232322 | ZF | X | 50 |
Existing slicer contexts:
What the measures should do:
Here's what I've tried (but doesn't work at all, unfortunately - also the rounding a little strange compared to the whole numbers presented in the sample table above, but the actual data involves smaller numbers, hence the way it is handled):
BulletChart_Min =
VAR group_act_wgt =
SUMMARIZE( FILTER ( ALLSELECTED( Table ),
Table[FilterGroup] = "X" ),
Table[Sector],
"Sector Average Quantity",
AVERAGE( Table[Quantity] )
)
VAR minimum =
MINX ( group_act_wgt, [Sector Average Quantity] )
RETURN
MROUND( minimum, -0.005 ) - 0.001
Separately, I would like to, for the same data, obtain a measure that allows me retrieve the minimum historical data, specific to the sector, thus the measure should:
Same for the maximum (which I should be able to figure one once some guidance on how to create the minimum measure is provided).
Have been tearing my head out over this, would appreciate any guidance.
Many thanks.
Bump - still attempting to work out a solution.
2 things. First,
Summarize the table by grouping using the column "sector" to sum "quantity"
Summing is not averaging and in your measure you're trying to average. Second, you should never use SUMMARIZE to calculate anything inside it. SUMMARIZE should only be used for grouping and nothing else. Instead, you should use the combination SUMMARIZE/ADDCOLUMNS, where SUMMARIZE groups and ADDCOLUMNS calculates. This is the only safe way to do what you're trying to achieve with just SUMMARIZE (it's a long story why this is so but you can find articles about it on www.sqlbi.com).
Here's a measure that might do what you're after:
Hi @daxer-almighty ,
Thanks - your solution worked like a charm. Regarding the use of "average" in my code attempt - yes that was my mistake, I was trying out various expressions for the sake of it.
Regarding my second query of finding the historical min/max for a certain lookback, do you think you can help with that as well? Given what you've already provided I will look into amending the query further to try to get what I need, and will update accordingly if I figure it out before you reply - albiet unlikely.
This
Return the minimum for each sector - say, over a period of the past 1-year
does not make too much sense or... you could phrase it more clearly. A measure can't return a table, only a scalar, so it's not possible to return a value for each sector if many sectors are selected. The values must be aggregated somehow.
Hi,
Thanks for being willing to help, much appreciated. I'll try again.
The measure will be returning a scalar, and this scalar will return the minimum/maximum of the total quantity recorded for, as an example, the past year.
The visualization (bullet chart) provides the row context (sector), for the grouping.
So what the measure does is:
I would also need to retain the KEEPFILTERS(Table[FilterGroup]="X") presented in your original solution.
Do let me know if there is any ambiguity I can elaborate on, if any.
Not sure if I understand it correctly, you want to find the min value of SUM ( Table[Quantity] ) on Month level in past 12 month? Do you have day level data and a Month column? It is better to post some sample data...and your visual to display it with Table[ParentGroup], Table[Group1]?
1Y_min =
VAR endDate = LASTDATE(Table[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate) - 1, MONTH(endDate), DAY(endDate) ) -- 1-year lookback
VAR filteredTable =
FILTER (
ALLEXCEPT ( Table, Table[ParentGroup], Table[Group1] ), -- Create a new table, ignoring the Date slicer filter
Table[Date] >= startDate &&
Table[Date] <= endDate
)
VAR t1 =
GROUPBY(filteredTable, [Month],"sumQuantity",SUMX (CURRENTGROUP, [Quantity] ))
RETURN
MINX ( t1, [sumQuantity] )
Hi @Vera_33,
Yes that is correct. It is currently just based on a date column, could I then use [Date].Month to do the grouping instead?
Here is some sample data:
Date | ParentGroup | Item ID | Group1 | FilterGroup | Quantity |
29-Jan-99 | A | 12431232 | ZZ | X | -100 |
26-Feb-99 | A | 12312333 | ZC | Y | 10 |
26-Feb-99 | A | 12334123 | ZA | X | -10 |
26-Feb-99 | A | 12342332 | ZB | X | -20 |
31-Mar-99 | A | 12321231 | ZA | X | 50 |
29-Jan-99 | B | 16453453 | ZA | Y | 100 |
26-Feb-99 | B | 13232322 | ZF | X | 50 |
Will try out your solution in a bit and get back to you, cheers.
As for the visual, Table[ParentGroup] is a slicer selection, so the visual will just have Table[Group1] as row context.
I don't think [Date].Month will work...I don't use this kind of auto date/time in Power BI...
Hi Vera, your measure didn't work, unfortunately.. I've updated the main post to include more details and a better set of sample data, hope it makes it easier for you. Thanks.
I used your updated dummy data, but I got different values, do you consider Sub Group as well? It should be -0.05% if no Sub Group, not -0.01% in your post. Am I missing something? It's better you can share a .pbix file, will pm you shortly.
Hi @Vera_33 ,
That's strange, since my dummy data is exactly what I've posted above. Even if I account for subgroups:
It seems like none of them should be returning -0.04%?
Unfortunately I'm unable to upload anything due to firewall restrictions, but I've tried my best to replicate from scratch what I'm showing here to hopefully make it easier to troubleshoot..
@Vera_33 I retried your solution, but there seems to be some error:
2m_min_vera =
VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate) - 1, MONTH(endDate), DAY(endDate) ) -- 1-year lookback
VAR filteredTable =
FILTER (
ALLEXCEPT ( Table2, Table2[SlicerGroup1], Table2[Group] ), -- Create a new table, ignoring the Date slicer filter
Table2[Date] >= startDate &&
Table2[Date] <= endDate
)
VAR t1 =
GROUPBY(filteredTable, 'Date'[Month],"sumQuantity",SUMX (CURRENTGROUP, [Value] ))
RETURN
MINX ( t1, [sumQuantity] )
EDIT:
I used CURRENTGROUP() instead of CURRENTGROUP and it seems to work! I'll try it on the actual data and report back in a couple of minutes:
2m_min_vera =
VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate) - 1, MONTH(endDate), DAY(endDate) ) -- 1-year lookback
VAR filteredTable =
FILTER (
ALLEXCEPT ( Table2, Table2[SlicerGroup1], Table2[Group] ), -- Create a new table, ignoring the Date slicer filter
Table2[Date] >= startDate &&
Table2[Date] <= endDate
)
VAR t1 =
GROUPBY(filteredTable, 'Date'[Month], "sumQuantity", SUMX (CURRENTGROUP() , [Value] ))
RETURN
MINX ( t1, [sumQuantity] )
The measure seems to only return the correct value when 30-Apr-21 is chosen. When any other dates are chosen, it seems like it returns the same value of -0.10%, when it should be -0.15% when 3-May-21 or 4-May-21 is chosen, for example.
I feel like I need to understand the value you want to return first...let's take 30-Apr-21, you need to sum up value per each month and find the Min.
It's -0.05% (-0.04%,0.02%,-0.03%), or -0.04% taking Sub_Group into account, otherwise I misunderstood the whole thing.
Hi @Vera_33 ,
Looking at the sample data I provided again it seems like it may be different from the one I'm using - apologies for that. I was using the RAND() generator in excel and must've accidentally refreshed it. In any case, I managed to build on what you provided and finally got it working. Here's the following code:
2m_min_vera =
VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate), MONTH(endDate)-2, DAY(endDate) ) -- 2-month lookback
VAR slicerSelection = VALUES( Table2[SlicerGroup1] )
VAR filteredTable =
FILTER (
ALLEXCEPT( Table2, Table2[Group] ), -- Create a new table, keeping the Group row context
Table2[SlicerGroup1] IN slicerSelection &&
Table2[Date] >= startDate &&
Table2[Date] <= endDate
)
VAR t1 =
SUMMARIZE(filteredTable, Table2[Date], "sumQuantity", SUM ( [Value] ))
RETURN
-- CONCATENATEX( t1, Table2[Group], "," ) Used for debugging
-- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) Used for debugging
FORMAT( MINX ( t1, [sumQuantity]), "0.00%" )
It seems like using ALLEXCEPT( Table2, Table2[SlicerGroup1]) was somehow not taking into account the slicer selection, since the debugging formulas indicated that slicergroup1 SG1_2 was being included in the filtered table as well.
I also swapped out the GROUPBY with SUMMARIZE mid-way, though they seem to serve the same function.
Thank you so much.
Thanks for the tip, I tried the [Date].month and indeed it didn't work, I'll try creating a separate date table and get back to you shortly. Cheers.
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 |
---|---|
111 | |
95 | |
77 | |
69 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |