Dax - Dynamic banding qty of customer by total value

[Anonymous]

Hi !

I am trying to create a table that Dynamically group summarize total by total range.

Sample Data table

Date	Customer	Value
01/jan	a	1
02/jan	b	1
03/jan	c	3
04/jan	a	4
05/jan	c	5
06/jan	b	4
07/jan	c	8
08/jan	b	8
09/jan	a	9
10/jan	a	10
11/jan	d	2

The results I am looking for is:

Value Range	Qty Customer	Total Value
Up to 15	2	15
15 to 20	1	16
Above 20	1	24

I am able to do that  by creating a summarized table and add a conditional calculated column with the ranges. But I lose the possibility of dynamic change the results by date slicer.

I have tried to create a Dax formula to summarize and group but with no success.

Does anyone have any ideas?

Thank you very much

[v-yuezhe-msft]

@Anonymous,

Create the following measures in your table, create a table visual using these measures and Customer field, then check if you get expected result when using date slicer to filter your visual.

Qty  customer = DISTINCTCOUNT(Table5[Customer])

Total Value = CALCULATE(SUM(Table5[Value]))

Value Range = IF([Total Value]<=15,"up to 15",IF([Total Value]>15 && [Total Value]<=20,"15 to 20","above 20"))

If the above formulas don't help, please post the formula you use to create summarized table and expected result you want to get when using Date slicer.

Regards,

[Anonymous]

Thank you for your reply, but it did not work.

When I use the formula for the range, it only shows the range “Up 15” because it evaluates just the total of the table.

Value Range	Customers	Value
up to 15	4	55

I can create a summarized table  using the formula: Table = SUMMARIZE('fact';'fact'[Customer];"Total";[TotalValue])

And then add a calculates column for the range : Range = IF([Total]<=15;"up to 15";IF([Total]>15 && [Total]<=20;"15 to 20";"above 20"))

It returns:

Customer Range Total

a	above 20	24
b	up to 15	13
c	15 to 20	16
d	up to 15	2

I have a slicer for the dates of the original "fact" table and I wish to change it and then the new table also change the values, summarizing only the values in the range of dates of the slicers. But I did not have success so far. 🙂

I appreciate any help.

[v-yuezhe-msft]

@Anonymous,

You don't create new table. Just create the above measures I provide in your table, then create a table visual as shown in the following screenshot. And you are able to use Date slicer to filter the visual.






Regards,

[Anonymous]

@v-yuezhe-msft

Thank you very much for your help. I really appreciate it.

I need a Column to count how many Customer I have per value range. So, when I remove the customer column from your example, total value and range are summarized and I have only one line, as I showed on my last post.

Do you think there is a workaround?

Thank you.

[v-yuezhe-msft]

@Anonymous,

I am afraid that your requirement can't be achieved. Because the Total value and range are evaluated based on the Customer field, and they are dynamically filtered by Date slicer. Without Customer field, the two measures will be aggregated.

Thus, when using the Date slicer to filter the visual, it is not possible to show dynamic range, Qty number and total value without Customer as you expect.

Regards,