Using Gauge as filter

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…