Power BI What-If Magic: Top N & Loan Tools | Ilgar Zarbaliyev
In this session, we explore the power of What-If parameters in Power BI to create dynamic analytical tools. You'll learn how to build a flexible Top N analysis and design an interactive loan calculator that responds to user input. This hands-on session demonstrates how What-If analysis can enhance decision-making and provide actionable insights in real time.
Visualisation for TopN suburbs per state
Hi, User requested to show only top 3/5/10 suburbs for each state, please suggest a chart where it picks TopN suburbs per state. where EMP STATUS=Current Sample data: EMP STATUS STATE SUBURB Terminated FY 22-23 WA South Lake Current WA East Fremantle Terminated FY 24-25 WA Leeming Terminated FY 25-26 WA South Lake Current WA Rockingham Current WA Leeming Terminated FY 23-24 VIC Ferntree Gully Terminated FY 22-23 QLD Mansfield Terminated FY 23-24 WA Meadow Springs Terminated FY 23-24 NSW Eagle Vale Terminated FY 23-24 VIC Koo Wee Rup Current WA Thornlie Terminated FY 24-25 NSW Raby Current WA Spearwood Terminated FY 22-23 WA Warnbro Terminated FY 23-24 WA Spearwood Current VIC Narre Warren North Current SA Linden Park Terminated FY 24-25 QLD Logan Reserve Current WA Canning Vale Current TAS Mangalore Current WA Waikiki Terminated FY 24-25 TAS Tinderbox Terminated FY 23-24 VIC Greenvale Terminated FY 23-24 WA Palmyra Current WA Armadale Terminated FY 22-23 WA Pinjarra Terminated FY 24-25 QLD Seventeen Mile Rocks Current WA Hamilton Hill Terminated FY 24-25 WA Success Current WA Mosman Park Terminated FY 23-24 QLD Kingston Terminated FY 24-25 QLD Loganlea Current WA Rivervale Terminated FY 22-23 WA Bibra Lake Terminated FY 25-26 WA Bibra Lake Terminated FY 22-23 SA Birdwood Terminated FY 22-23 WA Cooloongup Terminated FY 24-25 WA Armadale Current WA Byford Current WA East Perth Current WA Yangebup Terminated FY 24-25 TAS Blackmans Bay Current TAS Huonville Terminated FY 22-23 QLD Algester Terminated FY 24-25 WA Byford Terminated FY 22-23 WA Canning Vale Current WA Thornlie Current WA Bicton
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
