topic Re: How to have the same maximum of a sum, when we have a legend column in a stacked bar chart ? in DAX Commands and Tips

How to have the same maximum of a sum, when we have a legend column in a stacked bar chart ?

Azelie — Thu, 04 Aug 2022 15:25:36 GMT

Hello,

The values of the same measure seems to be different, when the parameters of the visuals are different.

The data come from a table in Project Online. It is resource demands day by day. I have several lines for each day because of many projects and many resources

I calculate the sum of values day by day and take the maximum of this sum on the period of a month in that case. I have calculated this with several DAX formulas. For exemple :

ResourceDemand Dailypeak 2 =

MAXX(

KEEPFILTERS(VALUES(timeSet[TimeByDay])),

CALCULATE(

SUM(

ResourceDemandTimephasedDataSet[ResourceDemand]

DISTINCT(

ResourceDemandTimephasedDataSet[TimeByDay]

)

Then I use a line and stacked column chart without any column legend. The maximum of the column is correct.

In another visual I use a line and stacked column chart with Project Name in the column legend. The maximum is bigger.

Could you help me to have the correct values with Project Name in column legend ?

Thank you in advance for your help

Nicole

Re: How to have the same maximum of a sum, when we have a legend column in a stacked bar chart ?

lbendlin — Fri, 05 Aug 2022 22:50:43 GMT

Rethink your use of DISTINCT() - it will yield different results at different granularity levels.

Re: How to have the same maximum of a sum, when we have a legend column in a stacked bar chart ?

Azelie — Tue, 09 Aug 2022 15:19:03 GMT

Hello lbendlin

How could I modify this DAX formula, to avoid this problem?

Moreover I have calculated the same measure with this other DAX formula:

MAXX(

KEEPFILTERS(VALUES(TimeSet[TimeByDay])),

CALCULATE(

SUM(ResourceDemandTimephasedDataSet[ResourceDemand]), ALL(ResourceDemandTimephasedDataSet[ProjectName],ResourceDemandTimephasedDataSet[ResourceName])

)

I have the same différence between the 2 line and stacked column charts

Nicole

Re: How to have the same maximum of a sum, when we have a legend column in a stacked bar chart ?

Azelie — Tue, 09 Aug 2022 16:01:54 GMT

Hello,

In the second visual it takes the maximum of the sum project by project.

Nicole

Re: How to have the same maximum of a sum, when we have a legend column in a stacked bar chart ?

lbendlin — Tue, 09 Aug 2022 18:20:47 GMT

Please provide sanitized sample data that fully covers your issue.

Re: How to have the same maximum of a sum, when we have a legend column in a stacked bar chart ?

Azelie — Wed, 10 Aug 2022 14:20:43 GMT

Hello lbendlin

This is a sample of data for the months of January 2023 and a generic resource. I replace Project Name and Task Name to hide real data.

We calculate the sum of Resource Demand day by day and take the maximum of this sum for the month of January 2023, in this sample of data

The first visual gives the Monthly Peak Units %. This is the real peak unit in the month.

The second visual gives the Peak Units per Project. The value of the maximum is too high : it calculates the maximum by project. Then, it calculates the sum of this value

Project Name	Task Name	Duration	Start Date	Finish date	Resource Demand	Monthly Peak Units %	Peak Units % per Projects
						449 this peak unit is between the 25th and the 28th of January	477 The value does not exist
107-01	T1	3,2 months	13 Jan '23	21 Apr '23	4%	4	4
140-15	T2	4 months	16 Sep '22	17 Jan '23	5%	0	5
140-15	T3	41 months	28 Jun '21	10 Dec '24	7%	7	7
184-08	T4	62 months	03 Jan '22	25 Mar '27	10%	10	10
195-02	T5	6 months	19 Sep '22	22 Mar '23	8%	8	8
195-02	T6	9 months	20 Dec '22	22 Sep '23	25%	25	25
195-02	T7	9 months	04 Jul '22	06 Apr '23	2%	2	2
300-01	T8	32 months	03 Nov '21	15 Jul '24	6%	6	6
416-01	T9	28 months	28 May '21	09 Oct '23	9%	9	9
416-01	T10	3 months	03 Oct '22	02 Jan '23	15%	0	15
430-01	T11	34 months	01 Mar '21	11 Jan '24	9%	9	9
433-03	T12	12 months	31 May '22	02 Jun '23	51%	51	51
476-01	T13	8 months	06 Sep '22	09 May '23	5%	5	5
482-06	T14	51 months	19 May '22	04 Sep '26	5%	5	5
482-08	T15	18 months	06 Jul '22	10 Jan '24	15%	15	15
483-01	T16	12 months	06 Jan '23	10 Jan '24	61%	61	61
493-01	T17	3 months	17 Nov '22	16 Feb '23	30%	30	30
495-01	T18	12 months	26 Jan '23	30 Jan '24	11%	11	11
497-01	T19	12 months	21 Jul '22	25 Jul '23	11%	11	11
497-01	T20	9 months	12 Apr '22	12 Jan '23	8%	0	8
497-01	T21	12 months	17 Jun '22	21 Jun '23	23%	23	23
497-01	T22	10 months	22 Aug '22	23 Jun '23	27%	27	27
497-02	T23	9 months	07 Dec '22	08 Sep '23	15%	15	15
497-03	T24	12 months	08 Dec '22	12 Dec '23	8%	8	8
497-03	T25	12 months	08 Dec '22	12 Dec '23	11%	11	11
497-04	T26	52 months	15 Mar '21	31 Jul '25	2%	2	2
549-16	T27	16 months	12 Nov '21	20 Mar '23	23%	23	23
594-18	T28	16 months	15 Oct '21	20 Feb '23	17%	17	17
594-18	T29	9 months	20 Jun '22	22 Mar '23	25%	25	25
Other	T30	24 months	03 Jan '22	10 Jan '24	10%	10	10
Other	T31	24 months	03 Jan '22	10 Jan '24	9%	9	9

I think I have a problem with the filter context. But I can not find how to correct this

Thank you very much for your help

Nicole

Re: How to have the same maximum of a sum, when we have a legend column in a stacked bar chart ?

lbendlin — Wed, 10 Aug 2022 19:11:59 GMT

Do you have a calendar table in your data model that identifies working days?

Re: How to have the same maximum of a sum, when we have a legend column in a stacked bar chart ?

lbendlin — Wed, 10 Aug 2022 20:16:16 GMT

When you add the project as the legend then the measure will have to filter by that project, which will then change the result ever so slightly. You would have to use separate charts per project (small multiples for example) to arrive at a more truthy representation. The difference is not that big though.

I used a slightly simple version of your measure

Peak Day = maxx(SUMMARIZE(Days,Days[Date],"s",sum(Days[Resource Demand])),[s])

see attached

Re: How to have the same maximum of a sum, when we have a legend column in a stacked bar chart ?

Azelie — Thu, 11 Aug 2022 15:32:16 GMT

Hello lbendlin,

Thank you very much.

You confirm to me that we can not have the same value by adding the projects as legend.

Nicole