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!
Dynamic Ranking for legend.
Hello all, So I have been working on a powerbi project and I am new to this so just exploring. I have orders table, customers table,date table along with few other tables and I have created measures for metrics like Order count, Order quantity, LIFR%,VOFR%,OTIF%, etc. What I want to do is, plot a linechart of every metric with time (from date table) but only top N customers. So I have done this till now. Basically I created a table with metrics name and then Selected_Metric_Value will return the metric value when i select it in slicer. I have tried using RANKX but the lineplot does not show the legend of customer names properly. For eg : If I select LIFR% on the slicer, I want the linechart to have top N customers lines vs timeframe. Then if I select Order count then I want the plot to dynamically change the data and legend both. MetricsTable = DATATABLE( "Metric", STRING, { {"Order Count"}, {"Order Lines Count"}, {"Order Quantity"}, {"LIFR%"}, {"VOFR%"}, {"OTIF%"} } ) Selected_Metric_Value = SWITCH( TRUE(), SELECTEDVALUE(MetricsTable[Metric]) == "Order Count", [Total_orders], SELECTEDVALUE(MetricsTable[Metric]) == "Order Lines Count", [Total_order_lines], SELECTEDVALUE(MetricsTable[Metric]) == "Order Quantity", [Total_order_quantity], SELECTEDVALUE(MetricsTable[Metric]) == "LIFR%", [LIFR%], SELECTEDVALUE(MetricsTable[Metric]) == "VOFR", [VOFR%], SELECTEDVALUE(MetricsTable[Metric]) == "OTIF%", [OTIF%], BLANK() )
Cumulative Total data points with no data (0s) display as the maximum issue
I am trying to develop a Ticket Sales cumulative line graph sectioned out by how many weeks before the event the tickets were bought. I used the following DAX command to get the cumulative total. And here is the graph that results from this measure. The issue that appears is the data points of 0 display as the maximum, or what is known as the final total at 0 Weeks Out from the event. What is even more confusing is that the cumulative total measure recognizes those data points as 0s because let's say the final total is 100; those 0s show up as 100 but the measure does not count them because it knows they are 0s. This problem is even more apparent when I insert a legend. All of these spikes are weeks where no tickets were sold, and instead of maintaining the past week's total, they spike to the maximum (final total) and are not included in the measure's calculation. I have also considered excluding these data points that spike, since they would be 0 anyway, but there are over 1500 that would need to be excluded and after 50, PowerBI gives me an error. Is there anything I can include in my DAX command to make these "spikes" smooth out and carry over the past week's total?
Legends in a graph
Hi there. I am currently comparing time periods graphically. However, when I put the agent or self-management legend to divide the bar in two as in the first image, it does not allow me. Is there any way I can put these two features on the same bar and not split it apart (like in chart two)?
Diversity report - Using Measure in Legend of graph
I am trying to build a Diversity report for my organization. A simple example of the data I work with: Candidate IDGenderOrg lvl1Org lvl 2Org lvl2ShortlistedInterviewedOffered 1 Male Sport Squash Ball Yes Yes Yes 2 Female Sport Squash Ball Yes Yes Yes 3 Unknown Sport Squash Ball Yes Yes Yes I added an excel file with the above and a power PI report here: https://wetransfer.com/downloads/e15fd9ac80e4da54360195bf5c5552c420221125151306/8f48a8bb5a0a74c98be0f1c5aa6788f120221125151323/c20328 I need to show the diversity data for each characteristic Broken down for each recruitment step (shortlisted, Interview, Offer). As we cannot use measures in the x Axis of a graph I started by Adding a new column (column1) to use as reference: with each recruitment step. Using Dax I then created the below measure: Measure test = VAR Short = CALCULATE( SUM( Table1[Candidate Sum] ), Table1[Shortlisted] ="Yes") VAR Inte = CALCULATE( SUM( Table1[Candidate Sum]), Table1[Interviewed] ="Yes") VAR Offe = CALCULATE( SUM (Table1[Candidate Sum]), Table1[Offered] = "Yes") RETURN SWITCH( SELECTEDVALUE( 'Recrtuiment steps'[Column1]), "Shortlisting", Short, "Interview", Inte , "Offer", Offe ) This returns the Diversity data perfectly https://ibb.co/mtdDFtn Now my issue is that I need to stop the graph from showing the legend data (Gender) when the value is less than 5. The problem is that the legend cannot accept a value. Adding a if less than 5 works in removing the data: less than 5 test = VAR Short = CALCULATE( SUM( Table1[Candidate Sum] ), Table1[Shortlisted] ="Yes") VAR Inte = CALCULATE( SUM( Table1[Candidate Sum]), Table1[Interviewed] ="Yes") VAR Offe = CALCULATE( SUM (Table1[Candidate Sum]), Table1[Offered] = "Yes") RETURN SWITCH( SELECTEDVALUE( 'Recrtuiment steps'[Column1]), "Shortlisting", IF(Short < 5 ,Blank() , Short) , "Interview", IF(Inte <5 , Blank() , Inte) , "Offer", IF( Offe < 5 , Blank() , Offe ) ) This DAX will remove the data completely if less than 5. Good start but now it looks like we don't have any small numbers instead of hiding them. https://ibb.co/SBYQQ1w What I need is for the legend to be amended to merge/group categories that are less than into a new "Hidden" value. So instead of (picturing the graph): Non Binary 3% - 3 Unknown 2% - 2 Male 50% - 50 Female 45% -45 shortlist I currently get: Male 50% - 50 Female 47% -45 shortlist I need: Hidden 5% Male 50% - 50 Female 45% -45 shortlist Is this possible, or am I not using the correct approach? My set of raw data is bigger but I only selected this to understand the logic. Thank you
