Forum Discussion
Creating Dynamic Revenue Range
I'm attempting to create a measure so I can appropriately bucket companies into $ ranges:
$1 - $5,000
$5,000 - $10,000
$10,000 - $20,000
Over $20,000
But the tricky part is doing it based on a date slicer, so depending on the date selection companies could move between categories. I currently have a table that looks like this:
So I have a date slicer that is currently set to 12/18/2017-12/18-2018 and the $ range I'm currently looking at is $10,000-$20,000. My 'Dynamic Revenue' formula is taking invoice dates into account and ensuring they fall within my date range I've selected:
My 'Revenue' column is just the grand total for each company regardless of dates selected.
My ' $ Range' formula looks like this (which isn't working properly):
My aim is taking this info:
And bucketing the companies into the appropriate categories. So if the above range is looking at dates of 12/18/2017-12/18/2018, I would need some kind of measure/column that is placing these companies into a category of $1-$5,000 since that is what they made between those dates. The below screenshot is determining their $ range based on their grand total which isn't what I'm looking for:
I'm trying to give end users the ability to select any date range they'd like, along with a slicer of the $ Ranges so they can see how many companies made X amount of money between X dates.
Hi Anonymous,
Sorry for the delay.
If I understand your scenario correctly that you want to create a date slicer and a range slicer to filter the companys which belongs to the range during the period.
It seems that you want to calculate the dynamic Revenue, that should be a measure.
However, as we know that we cannot use measure as the slicer. So I'm afraid we cannot create the Dynamic Revenue Range to achieve your requirement.:smileysad:
Best Regards,
Cherry
3 Replies
v-piga-msftResident Rockstar
Hi Anonymous,
Sorry for the delay.
If I understand your scenario correctly that you want to create a date slicer and a range slicer to filter the companys which belongs to the range during the period.
It seems that you want to calculate the dynamic Revenue, that should be a measure.
However, as we know that we cannot use measure as the slicer. So I'm afraid we cannot create the Dynamic Revenue Range to achieve your requirement.:smileysad:
Best Regards,
Cherry
AlB my pbix file is connected to our SQL database :smileysad: I will try to keep it simpler by looking at 1 company. So on my revenue table is this:
So this company from May 2014 - Today has generated a grand total of $25,203.43. So if I were to bucket this company into one of the category ranges, it would obviously be in the $20,000+ range.
Now let's say I want to see how much money this company made from May 2016-Today. That would look like this:
So now the total is $15,996.45. So now when looking at this, I would want this company to now be bucketed in the $10,000-$20,000 range since that is what they made between May 2016-Today. Is making a measure like this possible? I have my revenue table connected through a date table through an inactive relationship (Invoice Date->Date) :
So if a user selects a time period of one year (Date table used as slicer) for instance, and let's say they choose the $10,000-$20,000 range. They would be looking at all companies in that one year period that had a total revenue of $10,000-$20,000, despite if their overall grand total (ignoring all dates) is over $20,000.
