Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I'm trying to create a filtered table which gives me the "Top N" customers based on a measure.
--- Issue description ---
I've recreated the problem with dummydata, however I appear to be unable to share it due to community policy... The table I have however is rather simple:
- Date column ranging from 1/01/2019 to 1/12/2019;
- Customer column (customer A - customer Z) -- I've generated random numbers so these include both positives and negatives;
- Revenue column for the respective customers and months.
So basically, the table contains customers A - Z who all have revenue in every month of 2019 (can be both positive and negative). Pretty straightforward.
I've created a simple measure which takes the revenue of the current month and subtracts it from the revenue of the previous month, in order to map possible growth with customers, MoM:
Revenue difference previous month = CALCULATE(SUM(DummyData[Revenue])) - CALCULATE(SUM(DummyData[Revenue]);PREVIOUSMONTH(DummyData[Date]))
So now I have a simple table which gives me the monthly revenue per customer.
Next, I add my measure to the table. So far so good. It gives me an additional column which shows me if a customer increased in revenue or not.
Thereafter, I try to filter the visual (table) on Customer with Filter type "Top N", to show me the Top N (e.g. top 5 customers) By value [my aforementioned measure].
--- Expected result ---
The expected result should be the top 5 customers based on adding all the measure's result for the respective monthly revenue per customer. However, I've simulated the expected result in Excel, and it would appear that the result I'm getting is the same as when I'd be filtering on my "Revenue" column. It doesn't seem to take into account the measure itself.
To further elaborate: if customers A, B, C, D and E have the highest result according to my measure, and customer F, G, H, I, J have the highest Revenue (the data in my dummy column), using my method mentioned above results in customers F, G, H, I, J being filtered.
I've tried various solutions, ranging from creating a summarized table and/or calculated table to using 'What-if' parameters (RANKX). So far, none resulted in the result I am looking for.
Note: I am aware that the total of my measure in the table itself is incorrect, that, however, is of no interest to me. That's why it could as well be hidden.
I stumbled upon the following post, which appeared to be similar, but it didn't resolve my issue:
https://community.powerbi.com/t5/Desktop/Top-N-filter-for-measure/m-p/241508#M107193
Many thanks in advance.
Solved! Go to Solution.
Hi @Anonymous ,
I have created a sample for your reference, please check the following steps as below.
1. Create a what if parameter.
Parameter = GENERATESERIES(1, 10, 1)
2. Based on that, create a measure and make the table visual filtered by it.
Measure =
VAR r =
RANKX (
ALL ( DummyData[Customer] ),
[Revenue difference previous month],
,
DESC,
DENSE
)
RETURN
IF ( r <= Parameter[Parameter Value], 1, BLANK () )
Pbix as attached.
Hi @Anonymous ,
I have created a sample for your reference, please check the following steps as below.
1. Create a what if parameter.
Parameter = GENERATESERIES(1, 10, 1)
2. Based on that, create a measure and make the table visual filtered by it.
Measure =
VAR r =
RANKX (
ALL ( DummyData[Customer] ),
[Revenue difference previous month],
,
DESC,
DENSE
)
RETURN
IF ( r <= Parameter[Parameter Value], 1, BLANK () )
Pbix as attached.
Upon implementing your solution, the ranking itself still seemed to be based on the revenue figures instead of the summarization of the monthly differences. However, after tweaking the measure (Revenue difference previous month), I got the result I was searching for.
I changed the following:
Hi @Anonymous ,
kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Hi @v-frfei-msft
Here you'll find the dummydata I was referring to:
https://cronos-my.sharepoint.com/:f:/g/personal/oortsja_cronos_be/Et67lVCQbiRJpPmbMBHGy2gBJfyz0xx5Ka...
Kind regards
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |