"topn"
6 Topics
How to lock TopN results in a matrix regardless of calculation group selection
Hi everyone, I have a matrix visual in Power BI with the following configuration: Rows: d_customer_ranking[ranking group] d_customer_ranking[ranking corporate group] d_customer_ranking[ranking name] Columns: d_Calendar[Month] Time Intelligence[TimeIntelligence] ← calculation group Values: TopN Customers_test Here’s my main measure: TopN Customers_test = IF ( ISINSCOPE ( d_Customer_Ranking[Ranking group] ), VAR NumOfCustomers = 'TopN'[TopN Value] VAR RankingGroup = SELECTEDVALUE ( d_Customer_Ranking[Ranking group] ) VAR TopCustomers_byCorporateGroup = TOPN ( NumOfCustomers, SUMMARIZE ( ALLSELECTED ( 'd_Customer_Ranking' ), 'd_Customer_Ranking'[Ranking Corporate Group], "CurrentBaseValue", CALCULATE ( TOTALYTD ( [Current (base)], d_Calendar[Date] ), REMOVEFILTERS ( 'Time Inteligence' ) ) ), [CurrentBaseValue] ) RETURN SWITCH ( RankingGroup, "Best Customers", CALCULATE ( [Current (base)], KEEPFILTERS ( TopCustomers_byCorporateGroup ) ), "Others", IF ( NOT ISINSCOPE ( d_Customer_Ranking[Ranking name] ), VAR TopAmount = CALCULATE ( [Current (base)], REMOVEFILTERS ( d_Customer_Ranking[Ranking group] ), TopCustomers_byCorporateGroup ) VAR AllAmount = CALCULATE ( [Current (base)], ALLSELECTED ( d_Customer_Ranking ) ) VAR OtherAmt = AllAmount - TopAmount RETURN OtherAmt ) ), [Current (base)] ) These are the Calculation Group measures: PY YTD CALCULATE( TOTALYTD(SELECTEDMEASURE(), d_Calendar[Date]), SAMEPERIODLASTYEAR(d_Calendar[Date]) ) Contribution PY YTD DIVIDE( CALCULATE( TOTALYTD(SELECTEDMEASURE(), d_Calendar[Date]), SAMEPERIODLASTYEAR(d_Calendar[Date]) ), CALCULATE( TOTALYTD(SELECTEDMEASURE(), d_Calendar[Date]), SAMEPERIODLASTYEAR(d_Calendar[Date]), ALL(d_Customer_Ranking) ) ) Actual YTD TOTALYTD(SELECTEDMEASURE(), d_Calendar[Date]) Contribution YTD DIVIDE( TOTALYTD(SELECTEDMEASURE(), d_Calendar[Date]), CALCULATE( TOTALYTD(SELECTEDMEASURE(), d_Calendar[Date]), ALL(d_Customer_Ranking) ) ) YoY Growth YTD VAR CurYTD = TOTALYTD(SELECTEDMEASURE(), d_Calendar[Date]) VAR PrevYTD = CALCULATE( TOTALYTD(SELECTEDMEASURE(), d_Calendar[Date]), SAMEPERIODLASTYEAR(d_Calendar[Date]) ) RETURN DIVIDE(CurYTD - PrevYTD, PrevYTD) The problem is that the Top 20 customers shown in the matrix vary depending on the calculation group selected (e.g., PY YTD, Actual YTD). As a result, the Top 20 for Actual YTD are not the same as the Top 20 for PY YTD. The order and the number of customers showing should remain consistent and based on the current year YTD value. In other words, I need a way to decouple the TopN definition from the calculation group context. Any suggestions on how to "freeze" the TopN list to the YTD ranking? Thanks in advance for any ideas!Solved1.2KViews0likes6Comments
Price Average per selected TopN
Hello everyone, I currently have the problem that I need the average price per selected TopN. This results in price duplication and the average does not recognize that the individual values should be used. Example values look as follows: Article ID Article ID Competitor Price 123 34 1,50 123 35 1,56 123 36 1,56 ØN price = VAR SelectedN = SELECTEDVALUE('Average N'[Average N]) RETURN CALCULATE( AVERAGE(table[price]), TOPN( SelectedN, table, table[price], ASC ) ) If I enter TopN = 1, I get the expected value 1.50. But if I enter TopN = 2, I get the value 1.54 instead of 1.53. This happens because the value 1.56 occurs twice, but the calculation should differentiate according to TopN. Many thanks in advanceSolved1KViews0likes6CommentsTOPN + Others not aggregating properly with Legends
Hello dear Fabric Community! After many years of finding solutions here, it is my time to ask for help 🙂 I am working in a TOPN + Others problem, that is making my head spin. I am creating 3 different measures like this: X Measure= SUMX( SUMMARIZE( 'Original Table', 'Attribute 1 Table'[ID], 'Attribute 2 Table'[ID], "@X", [X]), [@X]). I am calculating X as X = CALCULATE( [Qty], NOT ('Original Table'[Category_1] IN { "Value 1", "Value 2" }), 'Original Table'[Category_2] = "ANOTHER_VALUE" ) I had to create them like this as my totals were not aggregating properly due to some overall quantities being positive, but when used with certain attributes for legends, the sum included some negatives. Anyway, that part seems to be working fine! I also created a Total Qty = X + Y + Z. My model is normalized, so I have a central fact table with lots of values, and a lot of additional dimension tables with details for some attributes. However, when aggregating and due to Power BI limitations on the 60 legends limit, when plotting some of this in a column chart it would not display accurate values. I arrived at the conclusion that I needed to use TOPN + Others for this charts, and possibly limit TOPN to have an overall number of legends on screen less than 60. 20 seems to be a sweet spot. Now comes the challenge! I watched some tutorials and arrived to the conclusion that a measure like this would do the trick: TOPN_ = VAR IDTopN = SELECTEDVALUE('TOP N'[TOP N]) VAR IsOtherSelected = SELECTEDVALUE ( 'Attribute 2 Table'[ID] ) = "Other" VAR tab = CALCULATETABLE ( VALUES ( 'Attribute 2 Table'[ID] ), ALLSELECTED ( 'Attribute 2 Table'[ID]) ) VAR _Rank = IF( ISINSCOPE ('Attribute 2 Table'[ID]), RANKX ( tab, [Total Qty] ) ) VAR TopNValues = SUMX ( TOPN ( IDTopN, ADDCOLUMNS ( tab, "@SumValue",[Total Qty] ), [@SumValue] ), [@SumValue] ) VAR OtherValues = SUMX ( ALLSELECTED ( 'Attribute 2 Table'[ID] ), [Total Qty]) - TopNValues RETURN IF ( IsOtherSelected, OtherValues, IF ( _Rank <= IDTopN, [Total Qty] ) ) Now, with the help of another table with TOPN values I can see my chart and it produces almost the same as [Total Qty]. There is a small difference, that sometimes I have been able to identify as this. If I do a table with Attribute 2 and my Total Qty, X, Y and Z values it looks like this (I have around 20k IDs for this attrbute): Total Qty X Y Z ID 1 ID 2 ID 3 ID 4 ID 5 ID 6 ... ... ... ... ... TOTAL 1 000 000 500 000 200 000 300 000 Imagine some values there that end up adding to the respective quantities. However, if I add my TOPN Qty, I get a slightly less qty, maybe something like 998 400. So I've been looking at the data and noticed that some of the "Attribute 2" ID's have negative values. I Filtered my table to display only Total Qty < 0 and see something like this: Total Qty X Y Z ID X - 900 - 500 - 100 - 300 ID Y - 700 - 400 - 200 - 100 Totals -1600 - 900 - 300 -400 And those are exactly my missing QTY from TOPN. So I have the following questions. Am I doing something wrong during the calculations that is somehow excluding the values for these negatives while calculating my measures, X, Y and Z? Am I doing something wrong during the calculations that is removing the conditions on X, Y and Z when doing the Total Qty or the subsequent TOPN + Others? Additional to this, I noticed that if my TOPN value is 5, and if I do some other aggregation on the column chart like, month or year, most values look OK (X, Y, Z are time dependent) and there's always an error with current month/year/Qtr (which is the one above). However, If I change my TOPN to 10, 15, or 20, I will also see some additional months/qtrs/years start to have issues. I was thinking this was due to the additional number of labels and for this I have not been able to identify any data points that might cause it, since even selecting 25 as my value does not increase my total number of legends to above 60. If additional details are needed, let me know. This has been giving me headaches for a bit now. Thank you!Solved1.2KViews0likes4Comments
Top 3 flags being removed
Hi, I'm needing help with a measure to get the top 3 support needs being removed on a month to month basis. I have a transactional table, "CUSTOMER_FLAGS" with customers, "CUSTOMER_FLAGS"[Customer], that have flags against them for support needs "CUSTOMER_FLAGS"[FLAG_ID]. Customers can have one or more flags against them. I have a date table "DATE"[Date], and relevant date columns in my table connected to this are "CUSTOMER_FLAGS"[ACTIVE_AT], "CUSTOMER_FLAGS"[EXPIRES_AT]. The "DATE"[Date] column is connected to "CUSTOMER_FLAGS"[ACTIVE_AT]. I also have "FLAG_REFERENCE" table with "FLAG_REFERENCE"[FLAG_NAME] to define what the flags are, [FLAG_ID] '103' = FLAG_NAME 'I need help with reading' etc and is connected via "FLAG_REFERENCE"[FLAG_ID] to "CUSTOMER_FLAGS"[FLAG_ID]. I need to see on a month to month basis, the top 3 support needs that are being removed; this is to see which support needs customers are needing less over time. I have struggled with this as it's both needing to get the support needs that are being removed over time, not the number of customers, but also with the added necessity to see it as the top 3 being removed. Many thanks in advance, Data-Papa.429Views0likes1Comment
Calculate Top 1, 2, 3, 4 & 5 Sales People Based on Total Sales Volume
I'm trying to create a separate measure for each rank in a top 5 sales people. The end goal is to use the new card visual to display the top 5 sales people, their sales volume and units (using reference labels and detail labels) My current code works for the top 1 salesperson, but I can't get it worked out for 2-5. #1 LO (Volume) Name = MAXX ( TOPN ( 1 , ADDCOLUMNS ( VALUES ( 'Loans'[Display Name] ), "frequency", CALCULATE ( SUM (Loans[Total Loan Amount] ) ) ), [frequency], 0 ), 'Loans'[Display Name] ) I was replacing the '1' after 'TOPN (' with 2, 3, 4 & 5 and I'm not getting the correct result. How can I achieve these measures?Solved630Views2likes2Comments
Calculate max value in date range and display only top result
Hi! I have tried to search for similar cases, but can not seem to find the correct solution for creating this measure. I have two tables - Date Dim and Fact Product. Dim date has date values and Fact Product as below (example data) (The Value-column is a calculated column and I can not filter Top N by this value in the visual.) ID Product Color Value Value Date 1 ABC123 Red 300 1/30/24 2 ABC123 Red 425 1/30/24 3 ABC123 Red 150 1/30/24 4 DEF456 Blue 225 2/2/24 5 DEF456 Blue 150 2/2/24 6 ABC123 Red 200 2/3/24 7 ABC123 Red 123 1/28/24 8 ABC123 Red 500 1/31/24 When selecting a date range from Dim Date in a slicer, I would like to calculate the values for the rows with same product name and color and find which Value Date this was found. I do not want to see all products that date, or all colors - only the one that meet the requirments. Expected result from example Selected date range 12/11/23 - 2/3/24 in slicer, visual should display: Product Color Value Value Date ABC123 Red 875 1/30/24 If selected date range is 2/2/24 - 2/3-24, visual should display: Product Color Value Value Date DEF456 Blue 375 2/2/24 Any idea on how this can be achieved? 🙂Solved496Views0likes1Comment