Once I have created this but I haven’t saved the code or power bi’s.
How can this be done again Sam ?
Really not sure of what your question is here.
There’s a relatively simple donut charts that are created easily via the visualization pallete?
Also not quite sure what a cauge is?? And also not sure what you mean by saving code? Which code??
Hi there Sam,
My excuse for mentioning a cauge I meant a donut chart
I saw your [Segment Your Customers Into Groups – Advanced DAX Example]. I seen the video and try to accomplish the same. Just some thoughts to understand what is happening.
I have sales data for customers over multiple years. I want to segment the groups into neg-low-med-high based on the sales invoices amount.
Following the example and created a group table:
When having the measure for the groups:
I try to understand the outcome of this logic… Does this means that based on the measure the customers will be ranked / categorized / grouped according to the MIN and Max values in the Group table.
So in my example when I also want to have customers with negative sales amounts that would not be possible…
Based on the logic in the formula…your customers will be placed inside a group if they individually evaluate to true on any row from the grouping table you have setup.
If they have sales between any of the min and max on a particular row then they will be placed in that group.
If they have negative sales they should go into that group based on what I’m seeing.
Thanks
Sam
Hi Sam,
I saw your videos on segmenting and they are kind of useful.
I have implemented now in a very simple way …
Group sales =
IF(
FACT_SALES_MONTHLY[Sales_Invoice_Value_EUR]
< 0,
“Negative”,
IF(
FACT_SALES_MONTHLY[Sales_Invoice_Value_EUR]
< 5000,
“Low”,
IF(
FACT_SALES_MONTHLY[Sales_Invoice_Value_EUR]
= 5000
&&
[Sales_Invoice_Value_EUR]
< 25000,
“Medium”,
IF(
FACT_SALES_MONTHLY[Sales_Invoice_Value_EUR]
=25000,
“High”))))
this is a column in the fact table. My feeling is that this needs more memory and can be done in measure table. However the sales is determined per sales record. How can I implement that extra table with the MIN an MAX ?
kind regards,
Sjaak
Hi Sjaak,
Speaking from one Dutchman to another. You do not need a calculated column but a so called disconnected table. As in your group table showing above, you can create a table, I do this in Excel or in Power Query and occasionally I come across negative values so I have to adjust the table.
Measure example=
Omzet in Omzetklasse =
CALCULATE( [Totaal Omzet];
FILTER(
VALUES( ‘HLM Combi’[opbrengst]);
COUNTROWS(
FILTER(
omzetklassezendingen;
‘HLM Combi’[opbrengst] >= omzetklassezendingen[van] &&
‘HLM Combi’[opbrengst] <= omzetklassezendingen[tot]))
> 0 ) )
In this example I have a measure Totaal Omzet (=SUM Opbrengst kolom in HLM Combi) and a table “omzetklassezendingen”. The grouping measure calculates for each “van” “tot” de opbrengst/omzet in the omzetklassezendingen. There is no relationship between the omzetklassezendingen table and other tables.
Paul Lucassen
Hi Paul,
Yes the Dutchman are everywhere 
I thought that while I was doing it. Coming from a background with Bi and using a lot of SQL I don’t have always the patience … Your piece of code helped me a lot.
I came up with my code
Hospital_Distribution = CALCULATE([Value Sales],
FILTER(
VALUES( ‘FACT_SALES_MONTHLY’[Sales_Invoice_Value_EUR]),
COUNTROWS(
FILTER(‘Hospital_Sales_Group’,
‘FACT_SALES_MONTHLY’[Sales_Invoice_Value_EUR] >= ‘Hospital_Sales_Group’[Min]&&
‘FACT_STRYKER_SALES_MONTHLY’[Sales_Invoice_Value_EUR] <= ‘Hospital_Sales_Group’[Max]))
0 ) )
Just one question remains how you handle the negative values?
Negative values are common? Creditnotes or incidental mistakes?. If I find that unexpectedly sales amounts show negative, I include a minus line in the table, example min max -10000-0, 0-500, 501-1000 etc. This way I can identify either a problem or take these into account as being legitimate.
Paul
Hi there Paul. I have no idea if these sales are legitimate. Unfortunatly there is in between people and i am not able to speak with the customer… for now it will be sufficient to have at least an inside in the numbrrs concerning negative sales… that solutikn with adding a - sounds good…