Need help in using Field Parameter in Top N filter and Conditional Formatting
Hi, I have a report that has conditional formatting and Top N filter on a static measure named IRR and it works perfectly. However, I want to change the measure to dynamic and allow user to select among a few other measures like DPI, RVPI and TVPI. I have created a Field Parameter and add all measures to it. It works for the bar chart at the bottom. However, I couldn't get the conditional formating to work. Also, the Top N filter based on that field parameter is not working properly. Can you advise how can I fix them, please? Thanks in advance. URL of my Power BI Report: https://github.com/chris-kc-cheng/ftk-powerbi/blob/main/Private%20Markets.pbix
TOPN N (DAX Formulas) Issues
I want to have a column or a line with TOP N (lets say 3) products sold in a period, working along the data calendar hierarchy. Like, what were the top 3 products sold per year, quarter, month, day according pivot table data hierarchy. Files and prints attached Please refer to pivot table in tab Pivot Table (6) to check the measure I tried to use. Problems Im facing: 1 I'v tried a bunch of top n tutorials and none of them work. Some use summarize, sumx, concatenatex… Question: I cant just use straight ahead TOP N like a function to have a column or line in the pivot table the 3 most sold products? I have to use some function to initialize the TOP N? 2 I did the relationship between tblProducts and tblOrders but the tblProducts does not show on the fields to select into the pivot table. Why? 3 =sumx(TOPN(3, SUMMARIZE(tblOrders, Calendar'[Date],topn sales,sum(tblOrders[Qty])),[topn sales],DESC),[topn sales]) tried this formula, but function sum [sum(tblOrders[Qty])] only accepts a table column and not another measure (used [Qty] so it could work). Id like to use the top 3 from Total Revenue X, how can I do that? Summarizing everything, how can I have the Top 3 products sold by period? Power Pivot Training - BEGIN - TS Fiscal Calendar.xlsb
TopN from a Summarize Table keeping the filters - Perfoming a Basket Analysis
Some description to understand my goal and problem (You can jump to the question). DESCRIPTION : I'm trying to perform a basket analysis. All the concepts are pretty cool, as always are, but when I tried to apply the calculations needed I failed in every effort. The amount of data is huge, over 30millions lines for a single year of sales for a single branch of the company. The calculation I'm trying to perform is based on a table, created with dax, that uses Cross Join to create all the possibilities (when sell product A, what are others sold together). fCombinations = FILTER ( CROSSJOIN ( VALUES ( fDetalheCupons[Produto]), SELECTCOLUMNS ( VALUES ( fDetalheCupons[Produto] ),"Produto2",[Produto] ) ), [ProCod]>[Produto2] ) The expression above generates a table with more than 5 hundred million lines. And Every Column/Measure with this table is imposible to be created due to memory issues (not by my set, which isn't the best, but I use a 6 core processor - Ryzen 5 - 3,6GHz combined to a 16Gb Ram - at the end of the day is my setup, but we need to deliver the results to regular computers that don't have this power of procesation). Some would say, short your data, but in fact I need to lengthen it. At least the transactions table. Than I came with an idea, generating a Summarized Table (even a calculated table) from the transaction table and take the TOP (1 or 2 or 3) products sold. fSummarized = TOPN ( 1, ( SUMMARIZE ( DetalheCupons, dProdutos[Produto], "Qtide", sum ( fDetalheCupons[Qtide] ) ) ), Qtide], DESC ) Then I use this table to cross join the "transactions" table, now the combinations expression is: fCombinations = FILTER ( CROSSJOIN ( VALUES ( fSummarized[Produto]), SELECTCOLUMNS ( VALUES ( fDetalheCupons[Produto] ),"Produto2",[Produto] ) ), [ProCod]>[Produto2] ) And It would solve my problem because now I would deal with max 3 products to combine the sales. IT WORKED FINE, TopN(1,). THE PROBLEM If i don't change the filter context at the transaction table I will always get the same topn 1 product. Then I added some slicers to slice the transaction table (figure 1) . But no matter what I select at the slicers, I Always get the the same product(52081) figure 2. THE QUESTION: How can I create a table to show the top products from the sliced transaction table, keeping the filters applieds in the slicers. In this case, the example, I should get a table with a single row with the product 17432 and not the 52081 (wich is the most sold product, but not on the category selected).How to show top 5 providers and top 5 services for each of these providers
Hi, I have this long list of provider names and services they provide. It's over 4k rows. Something similar to this: This is what I'm trying to achive: I have tried creating a new table using summarize and topN. I have also tried Rank and Ranxk. When it ocmes to ranking I got worng reuslts or all of the rows were showing value of 1. Please help.
Waterfall chart with Top N / Bottom N sales based on slicer
Dear PowerBI Community, Despite having checked multiple threads on applicable DAX formula’s I can’t seem to find the correct solution for what I’m searching for. I would greatly appreciate it if someone could help me. For a data source, I have a single dataset of customer names, time periods and sales values. I want to setup a waterfall chart whereby between the totals, I want to show the top-5 gainers in sales, top-5 declines in sales and have the sales change of all other customers grouped together in ‘Others’. I verified that this can only be achieved using DAX formulas (i.e. not with just visualisation options). The thing that complicates this is that I have a slicer whereby the user can select any (2 or more) time periods, so the DAX formula needs to be dynamic to accommodate any combination of time periods. Would appreciate any DAX help.Rank by group and order it by another related column table
Hey Community! I have those two tables: Movie_Details - contain: "Major Genre", "Director" Revenue - contain: "ROI" I want to get the movie with the highest ROI for each Major Genre and show the Director's name of the film. The tables are related by column name "Title". I tried to rank by group (Genre) that will be ordered descending by ROI to get all the 1's, but it didn't work. Where did I go wrong? Here's the DAX code: Rank Genre = RANKX(ALLEXCEPT(Movie_Details, Movie_Details[Major Genre]),MAXX(RELATEDTABLE(Revenue),Revenue[ROI]),,DESC) And here's an example of the ranking for the genre "Action" with descending ROI:
How to get the penultimate order date per customer from order data
Hi Guys, I want to ask how to take out the penultimate order date of each customer,I have tried using the following formula,but doesn't work penultimate order date= CALCULATE( MIN('order'[Order Date], TOPN( 2, 'order', 'order'[Order Date] ) )) thanks for all the tips and help @!
Filter for Top 60% Sub-Category
I wish to have a filter such that 1. There is a table where there are columns containing Product names, Product Sub-Category names, Order Value USD values corresponding to each Product Sub-Category, Number of Suppliers values corresponding to each Product Sub-Category. 2. Now I want to filter Product Sub-Category column by "Sub-Total of SUM of Order Value USD column for each Product" such that the Filtered Product Sub-Category Column will show only Top 60% Product Sub-Categories by "Sub-Total of SUM of Order Value USD column for each Product". Can someone help with the DAX Logic??
