Dynamic ranking of a parameter value
Hi! Would appreciate help figuring out how to create a measure that calculates the dynamic ranking of a parameter value. Let me provide context. I have a 'Transactions' table with columns <ID>, <Weeknum>, <Amount>, <Category>. I'm interested in looking at the aggregate amount per week per ID, and its ranking. My report will have a slicer filter where the user can select different categories, therefore my grouped table becomes dynamic. Additionally, the user is able to input a value as a parameter that represents a weekly aggregate amount. Let's call that parameter value the 'threshold'. The objective is to figure out what rank is this threshold out of the grouped table. Example of original 'Transactions' table ID Weeknum Amount Category 111 30 100 A 111 30 50 B 111 31 200 B 222 31 150 A 222 31 200 A 222 32 300 A Example of Grouped Table and ranking if filtered by Category A ID Weeknum Weekly Amount Ranking ASC 111 30 100 1 222 31 350 3 222 32 300 2 So if the threshold selected = 300, then my ranking measure should return 2. Was thinking of using RANK.EQ function with threshold as the 'number' argument, but I'm stuck at the 'ref' argument. I can't pass a summarized table as the ref argument as it gives me the error that the base table can't be found. Please let me know how can I modify this to achieve the desired result.
How to use SUMMARIZE inside a calculated column?
I'm trying to get information from aggregated version of my table into my original table as a column, but im not sure how to do it. Find the sample ecxample below The table contains 4 column - EMP_ID, DATE, MONTHLY SALARY, DESIGNATION I want to create new column called TOTAL SALARY which is the sum of salary for each employee available in data. There is time filter as well, like if I select 6 months in the filter visual the total salary should be populated as total six months salary. I couldn't find a way to do it in PBI using DAX/POWER QUERY. Please help me on this!! Thanks
Question about filters in summarize
Hello, I noticed an unexpected result in my DAX Formula that I can't explain and I'm pretty sure that I'm just missing an info. Here it is : IF ( ISINSCOPE ( GEOGRAPHY[REGION_NAME] ), SUMX ( FILTER ( SUMMARIZE ( INVOICES, GEOGRAPHY[REGION_NAME], "Spend geo", IF ( ISFILTERED ( DIM_CALENDAR[YEAR] ), [Spend], [Spend YTD] ), "# Invoices", [Invoice Quantity Period] ), [# Invoices] >= MinInvoiceValue && [# Invoices] <= MaxInvoiceValue && [Spend geo] <= MAX ( 'Spend Threshold'[Value] ) && [Spend geo] >= MIN ( 'Spend Threshold'[Value] ) ), [Spend geo] ) ) My problem is that the ISFILTERED function is always returning True even though there are no selection, I tried it on a blank page even to be sure no filter is hidden somewhere. My thought was that the FILTER function is biasing the result, but can't find any information to confirm my hunch. Thanks a lot
SUMX SUMMARIZE group by company in a table
Hi all. I have a simple table made of 2 fields and 1 measure: where _Sales Denominator YA ORG = CALCULATE( [_Sales YA ORG] , ALL(Company[Company], Company[Brand],Company[Sub Brand], Company[Variant]) , ALLSELECTED(Period[Period]) ,ALLSELECTED('Market Breakdown'[Country]) ,ALLSELECTED('Organic Claim'[Organic Claim]) , VALUES('W Segment'[W Segment]) , VALUES('Market Brkdown'[Hier Level Name]) , VALUES('Ad Hoc Analysis View'[Category]) , VALUES('Ad Hoc Analysis View'[Segment]) ) what I want to do is to delete the country field and obtain one row for each company with the sum of each _Sales denominator YA ORG for each company. For example I want this result: Company _Sales denominator YA ORG PRIVATE LABEL 3402952 STRAUSS GROUP 206 TCHIBO 36854 TONYS COFFEE ROASTERS 2895 VERGNANO 1131 WERTFORM 3987 WICKED JOE LLC 23592 WOSEBA 1035 So what I've tried is to modify the measure this way: _Sales denominator YA ORG= SUMX( SUMMARIZE( 'Ad Hoc Analysis View', 'Ad Hoc Analysis View'[Company], "x_per_company", CALCULATE( [_Sales YA ORG], ALL(Company[Company], Company[Brand],Company[Sub Brand], Company[Variant]), ALLSELECTED(Period[Period]), ALLSELECTED('Market Brkdown'[Country]), ALLSELECTED('Organic Claim'[Organic Claim]), VALUES('W Segment'[W Segment]), VALUES('Market Brkdown'[Hier Level Name]), VALUES('Ad Hoc Analysis View'[Category]), VALUES('Ad Hoc Analysis View'[Segment]) ) ), [x_per_company] ) But I obtain this table, with summarized values that are different from what I expect: Do you have any suggestion? ThanksSUMMARIZE two tables many to many Relationship
I'm trying to get this in Excel (Edit DAX in Table) and I need the column 'table2'[DesPro], but I can't figure it out how to do that. So far, I have: FILTER( SUMMARIZE( table1, table1[NF Saída], table1[Título], table1[Total], table1[Ult. Pgto], 'table2'[DesPro] -- where table1[NF Saída]='table2'[Nota Fiscal] with many to many relationship. A union table with all distinct values from both tables had been created ), table1[Ult. Pgto] <> BLANK()&&table1[Ult. Pgto] >= DATE(2023, 1, 1) ) I appreciate any help.DAX table to create a revenue funnel visual?
Hi! my main fact table is financial transactions which look pretty much like this: date type amount 10/12/22 revenue 100$ 10/12/22 expense 50$ 01/01/23 revenue 100$ 01/01/23 expense 30$ 05/01/23 revenue 150$ 05/01/23 expense 75$ I want to create a revenue funnel visual that shows the conversion between the revenue and the margins. from what I understand, I need to get a table at this structure ( I can drop the year but I want to keep it for future visualization) year type amount 2022 revenue 100$ 2022 margins (100$-50$) = 50$ 2023 revenue (100$+150$) = 250$ 2023 margins (100$+150$) - (30$+75$) = 145$ Then I will drag the type to the funnel group and the amount to the funnel value. How can I create this table using DAX? Thanks! Tal
