TOPN by Date
I'm trying to caluclate the top 7 Gross Adds by month. So I use the filter topn in the filter section which works fine. But I want to have another table next to it that adds 364 days to each top 7 dates, so as to project the future year top dates but on the same weekday. I have a slicer that is Month Year. So I try a measure that results in error that "expression refers to multiple columns". TopFutureDates = TOPN ( 7, ALLSELECTED( 'Subscriber Activity'), [Gross Adds], DESC ) So I tried: TopFutureDates = CALCULATE ( SUMX(VALUES('Calendar'[Calendar Date]),[Gross Adds]), KEEPFILTERS ( TOPN ( 7, ALLSELECTED( 'Subscriber Activity'), [Gross Adds], DESC ) ) ) This results in a sum total of the whole month not top 7. If I include the date in the Columns pane it shows all of the dates for the whole month. I'm wanting it to return the top 7 values along with the date. Then add 364 days to each date it finds. Thanks!
TOP N and Others in several subcategories
Hello guys, I have organizational hierarchy table, which contains 3 levels: path_5, path_6 and ziskatel. Then I have data table, where is id of lowest level of hierarchy (id_ziskatel). I have created meassure, which calculate cumulative sum of profit. 12M príjem zo štruktúry = VAR EndDate = MAX(tbl_kalendar[datum]) VAR StartDate = EDATE(EndDate,-12)+1 VAR Result = CALCULATE( SUM(data[prov]), DATESBETWEEN(tbl_kalendar[datum],StartDate,EndDate), data[id_prijem]=2) return Result Afterall, I want to display in matrix table visualization in Power BI TOP 10 subject according to "12M príjem zo štruktúry" and others summarize into subject "Others". I tried code: TOP N = VAR Table_TOP10_ziskatel = TOPN(10, ALLSELECTED(tbl_rebrik[ziskatel]), [12M príjem zo štruktúry]) VAR TOP10_ziskatel = CALCULATE( [12M príjem zo štruktúry], KEEPFILTERS(Table_TOP10_ziskatel)) VAR Others_ziskatel = CALCULATE( [12M príjem zo štruktúry], ALLSELECTED(tbl_rebrik[ziskatel])) - CALCULATE([12M príjem zo štruktúry], Table_TOP10_ziskatel) VAR Current_Product_ziskatel = SELECTEDVALUE(tbl_rebrik[ziskatel]) VAR Result_1 = IF(Current_Product_ziskatel<>"Ostatní",TOP10_ziskatel,Others_ziskatel) VAR Table_TOP10_path_6 = TOPN(10, ALLSELECTED(tbl_rebrik[path_6]), [12M príjem zo štruktúry]) VAR TOP10_path_6 = CALCULATE( [12M príjem zo štruktúry], KEEPFILTERS(Table_TOP10_path_6)) VAR Others_path_6 = CALCULATE( [12M príjem zo štruktúry], ALLSELECTED(tbl_rebrik[path_6])) - CALCULATE([12M príjem zo štruktúry], Table_TOP10_path_6) VAR Current_Product_path_6 = SELECTEDVALUE(tbl_rebrik[path_6]) VAR Result_2 = IF(Current_Product_path_6<>"Ostatní",TOP10_path_6,Others_path_6) VAR Table_TOP10_path_5 = TOPN(10, ALLSELECTED(tbl_rebrik[path_5]), [12M príjem zo štruktúry]) VAR TOP10_path_5 = CALCULATE( [12M príjem zo štruktúry], KEEPFILTERS(Table_TOP10_path_5)) VAR Others_path_5 = CALCULATE( [12M príjem zo štruktúry], ALLSELECTED(tbl_rebrik[path_5])) - CALCULATE([12M príjem zo štruktúry], Table_TOP10_path_5) VAR Current_Product_path_5 = SELECTEDVALUE(tbl_rebrik[path_5]) VAR Result_3 = IF(Current_Product_path_5<>"Ostatní",TOP10_path_5,Others_path_5) VAR Result = SWITCH( TRUE(), ISINSCOPE(tbl_rebrik[ziskatel]) && ISINSCOPE(tbl_rebrik[path_6]) && ISINSCOPE(tbl_rebrik[path_5]),Result_1, ISINSCOPE(tbl_rebrik[path_6]) && ISINSCOPE(tbl_rebrik[path_5]),Result_2, ISINSCOPE(tbl_rebrik[path_5]),Result_3) return Result But It works only for first level of hierarchy "path_5". If I drilldown to path_6, I will get just TOP 10 in path_6, but not the subject others ("Ostatní"). Can you please help me? Also I would like to rank those TOP 10 subjects according to amount descending and the others puts in the end. Thank you.Dynamic Top N Parameter doesn't work when adding a LEGEND to a Stacked Bar Chart
Link to pbix: Dynamic TopN with Legend not working.pbix In my sample, I have a horizontal stacked bar chart (Sales by City) that interacts correctly with the dynamic Top N parameter: I used the following rank measure: Rank_City = RANKX( ALLSELECTED('Dim_City'[City]), [Sales Amount], , DESC, DENSE ) And a measure applied to the chart as a filter to activate the dynamic Top N: TopNCity = IF([Rank_City] <= 'TopN'[TopN Value], 1, 0) All is well until adding a sub-category as a legend breaks the dynamic top N: How do I make sure the chart still displays the correct number of cities even with a sub-category applied as a legend? I have attached a link to the sample pbix. Thank you!
TOPN DAX without filter
I have an issue with the TOPN method. I have a table with a few columns with different Water parameters, they were filled in on different days (so they have gaps) "InsertDate" column shows these dates. it is also based on different tanks. Tank InsertDate HardnessF Alkalinity ph Conductivity Temperature 111 13-03-24 11:14 25.54 6.88 808 15 111 12-03-24 9:27 26.07 6.94 807.2 15.3 111 11-03-24 10:42 24.29 2.96 6.97 803.5 15.6 112 11-03-24 10:40 24.29 2.96 6.97 803.5 15.6 112 11-03-24 10:34 22.9 1.73 7.05 511.9 11.5 112 11-03-24 10:30 26.3 2.97 6.94 831.9 15.4 112 11-03-24 7:15 7.772 11.9 I made a Line charts which show me a trends of value for every parameter based on Insert date. and two slicers for user: "InserDate", and "Tank" to select each tank. ISUEE: I would like to measure the +/- 3sigma value for each parameter based on only last 10 samples, independently/without the "InserDate" slicer but I can't handle how to remove a filter from this "InserDate" column only for TOPN method. Here is my code. +3sigma_HardnessF_last10samples = VAR lastxsamples = TOPN (10, WaterData,'WaterData'[HardnessF],DESC) RETURN IF(SELECTEDVALUE(WaterData[Location])="Poland", CALCULATE( AVERAGE('WaterData'[HardnessF])+(3*STDEV.P('WaterData'[HardnessF])),lastxsamples),"") I will be grateful for any help.
TOPN with parameters & slicers
Hello all, I have a table with weather details. Every day, for 50 years, details like max. temp & mm. rain are updated. Below you see a screenshot of an overview page, with on top the parameter (12 items) and on the right slicers for year / decade / month / date etc. I would like only the bars for the top10 results to be shown. For example the top 10 days/weeks/months/years based on total mm. rain for the past 50 years ... or the top 10 days/weeks/months/years based on the average max. temp in the past 50 years ... and so on. Besides that would it be nice of the bar of this year would have an slighty different color. Would appereciate your help / tips very much. Thkx in advance, Rud.Top Ranked in various metrics
I have a number of current ranking measures which detail top salesperson, top selling region etc which are working well within a clustered column visual. What I would like to do with each metric is display some additional information. So in best selling region, I'd like to display the region name (this is as a tooltip), but probably through concantenation, also display the best selling salesperson in that region and the total sales for that person. I have all these currently as measures along the lines of this... Top Ranked Region = CALCULATE(SELECTEDVALUE(tblRep_List[Region]), TOPN(1,ALL(tblRep_List[Region]), [Amount Measure],DESC)) ...and have tried some concatenation to merge the measures togeher but obviously I'm then displaying the correct highest selling region , but not the correct salesperson within that region. Can anyone advise as to the best way to approach this? Thanks
Display top 10 Longest Calls in a table
Hi I guess my lack of dax knowledge is issue here. I need to be able to pull the 10 longest call wait times. to display under neath a KPI. By default the KPI does a caulation and shows the value for last month, undereath it shows previous 3 months in a line chart. What I'm struggling to do is get table below to show Callref, Start Time,End Time , Queue time in sec, Talk time, But broken down to show the top 10 wait times for current month. However if one of the points on the line chart is selcted then top 10 wait times for that month should be shown instead. Below is dax used to select the current date if not filtered. However I then need to work out the top10 for that month. But I'm having trouble in understanding how to get TopN to work as measure. IF ( ISFILTERED ( CS_dim_CALENDAR_CallStartDate[Date] ), MAX ( CS_dim_CALENDAR_CallStartDate[Date] ), MAX ( CS_fact_Call[CallStartDate] ) ) ps I'd love to supply a file with dataset but it has 40+different fac & dimention tables in and is not something I can share. I'm hoping my expliantion might help someone point me in the right direction
