Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ajaydavidluke
Helper I
Helper I

Turnover Groups

Good afternoon Community, 

 

I have a connundrum that I wanted some help with 🙂 

 

I have a simple list of turnover by account 2022.  What I would like to do is  create a measure that sums the total turnover by account and give them a category based on total turnover as follows:

Group 1 'Std' - <£5k

Group 2 'Progressive' - >£5k <£25k

Group 3 'Target' - >£25k <£50k

Group 4 'Allaince' - >£50k <£150k

Group 5 'Prime' - >£150k 

 

My aim is to then put this data in a map using the above data as the legend:

 

ajaydavidluke_0-1665665411756.png

ajaydavidluke_1-1665665473937.png

 

Any ideas here wouyld be greatly appriciated 🙂

 

9 REPLIES 9
saravanan_p
Resolver III
Resolver III

Hi,
Its straight forward.

First create buckets in calculated column to capture category by using nested if. There you get group 1,2,3, etc according to your needs.

Now drag and drop turnover in values and maps with location and legends as this calculated column.

Hope its clear and kindly mark it as solution 

 

Thank you for the response, please correct me where I am wrong, I'm new to this functionality, your help is greatly appriciated 🙂  from the below I am not getting the desired result!

 

ajaydavidluke_0-1665670243099.png

ajaydavidluke_1-1665670482425.png

I should be seeing more colours on the map?

 

I've also tried this but same results:

ajaydavidluke_0-1665671907360.png

 

Hi,

Thats perfect. And your results are correct. I see different color in maps. Check deeply, you see legends with different colors, but data is more for blue. You can obserev 1 or 2 orange color spots too. So its working. 
Kindly mark as solution , as this would support our community. 

The issue I can see is that I have accounts with large turnovers but this group is no where to be seen, for example the biggest account is >750,000 but this isnt reflected in the map, ideally I would want only one colour to represent the final figure?

ajaydavidluke_4-1665726138270.png

 

 

I created a group >150k and this should be visible on the above account

ajaydavidluke_3-1665726048469.png

 

Dear I believe for the same location , there may be different account codes having more categories.
Thats the reason you see 3 color for same location.
Can you bring location in table along with account code and turnover, this would conclude and solve your use case

Here is a measure I have been working on:

ajaydavidluke_0-1665737016271.png

When I create a table view, my measure is accurate but my calclulated column is incorrect:

ajaydavidluke_1-1665737248314.png

I cannot insert my measure into the legend but when I insert the same calculated column I get the adverse results.

 

Here is my full table:

ajaydavidluke_2-1665737456637.png

I've tried many many combinations but the legend remains incorrect and I cannot present the data as it not accurate enough. 

Lets strip this all back and focus in on the data set, it has multiple invoice sku lines, line value represents SKU QTY x UNIT PRICE and this is done at source.

 

I then created a column TOTAL TURNOVER which is SUM(

'Daily Turnover 2022'[Line Value]), I used this in the calculated column and does not work.
 
I've also changed the column to 
CALCULATE(IF(SUM('Daily Turnover 2022'[Line Value]) <= 5000, "<5k",
IF(SUM('Daily Turnover 2022'[Line Value]) > 5000 && SUM('Daily Turnover 2022'[Line Value]) <= 25000, "5k > 25k",
IF(SUM('Daily Turnover 2022'[Line Value]) > 25000 && SUM('Daily Turnover 2022'[Line Value]) <= 50000, "25k > 50k",
IF(SUM('Daily Turnover 2022'[Line Value]) > 50000 && SUM('Daily Turnover 2022'[Line Value]) <= 150000, "50k > 150K",
IF(SUM('Daily Turnover 2022'[Line Value]) > 150000 && SUM('Daily Turnover 2022'[Line Value]) <= 10000000, ">150k")))))).
 
Doesnt work but the measure does?  What is missing from the column that makes it not work the same as the measure?

 

Remove calculate and sum from your code which is used to create calculated column. It should work

@saravanan_p  Eagle eyes. Nice observation. Good work!!. 
@ajaydavidluke Hope its clear, kindly mark this as a solution 

That awesome. So speedy. I was about to type the same. Congratz.

@ajaydavidluke  Kindly mark it as solution , keeps the community healthy

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors