topic Re: sum of delta values in a date range in DAX Commands and Tips

sum of delta values in a date range

fb7 — Fri, 04 Mar 2022 14:05:00 GMT

Hi,

I'm new in Power BI and I work in a Printers Sales Company.

I have generated a table from a SQL Query with this values:

[Machine Number] - [Counter] - [Reading Date] - .....

XX123 - 12 - 01/01/2022

XX123 - 20 - 02/01/2022

XX123 - 30 - 03/01/2022

XY456 - 25- 01/01/2022

XY456 - 60 - 02/01/2022

XY456 - 100 - 03/01/2022

in Power BI I have a Visual with a Slicer for Date Range value Selection and a Table View with Min / Max Counter values for every Machine:

[Machine Number] - MIN[Counter] - MAX[Counter] - Delta(MAX-MIN)/Number of Counters

this works great!

But at the End I have the "Summe" row und that calculate the Min and the Max of this View and not the Sum of every calculation in previus rows!

in my example: Max -> 100 (from XY456) - Min -> 12 (from XX123) ---> that's wrong! <----

My Dream: Sum(Max) -> 130(100+30) - Sum(Min) 37(12+25)

How can I calculate the sum of min/max for every machine in view (from date range slicer selection)???

Thank's very much.

FB7

Re: sum of delta values in a date range

littlemojopuppy — Fri, 04 Mar 2022 18:54:21 GMT

Hi fb7

Try this...

Dream Calculation:= VAR MachineSummary = ADDCOLUMNS( CALCULATETABLE( VALUES(RawData[MachineNumber]), ALL(RawData) ), "Minimum", [Counter Minimum], "Maximum", [Counter Maximum] ) VAR TotalMinimum = SUMX( MachineSummary, [Minimum] ) VAR TotalMaximum = SUMX( MachineSummary, [Maximum] ) RETURN TotalMaximum - TotalMinimum

I think this is the result you were looking for?

The DAX is in Power Pivot in the attached Excel file.

Re: sum of delta values in a date range

Anonymous — Tue, 08 Mar 2022 09:14:42 GMT

Hi fb7 ,

You can create a measure as below to get the delta value, please find the details in the attachment.

Delta = VAR _tab = SUMMARIZE ( 'Table', 'Table'[Machine Number], "@delta", CALCULATE ( MAX ( 'Table'[Counter] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Machine Number] = EARLIER ( 'Table'[Machine Number] ) ) ) - CALCULATE ( MIN ( 'Table'[Counter] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Machine Number] = EARLIER ( 'Table'[Machine Number] ) ) ) ) RETURN SUMX ( _tab, [@delta] )

Best Regards

Re: sum of delta values in a date range

fb7 — Tue, 08 Mar 2022 09:53:06 GMT

Thanks littlemojopuppy but I don't have in Table a "Counter Minimum" and a "Counter Maximum" value:

I need always calculate from a date range slicer.

In my table there is only a "Counter" column with "registration date"

Re: sum of delta values in a date range

fb7 — Tue, 08 Mar 2022 10:23:40 GMT

thanks Anonymous for your answer.

the calculation works good on every row, but the sum and the grafic view return other values.

I need to apply the calculation on date range filter (in this example 30/06/2021 - 08/03/2022).

best regards.

Re: sum of delta values in a date range

littlemojopuppy — Tue, 08 Mar 2022 14:48:32 GMT

fb7 those would be measures that correspond to these

Re: sum of delta values in a date range

Anonymous — Wed, 09 Mar 2022 01:48:16 GMT

Hi fb7 ,

Please create another new measure as below and put this new measure to to replace the original measure [Delta]:

Measure = SUMX ( GROUPBY ( 'Table', 'Table'[Kundenummer], 'Table'[CUSTOMER_NAME], 'Table'[MACHINE_NO], 'Table'[METER_TYPE], 'Table'[DESCRIPTION] ), [Delta] )

And about how to deal with the incorrect total values problem, you can refer the method in the following links to resolve it.

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

If the above ones still can't help you the correct result, please provide some sample data of involved tables on the visual and your expected result with backend logic and special examples. Thank you.

Best Regards

Re: sum of delta values in a date range

fb7 — Mon, 14 Mar 2022 14:12:06 GMT

thanks Anonymous ,

that's work on the table view.

I've also a diagram view for total split on meters but that don't show right values:

Measure3 = calculate(DIVIDE(

SUMX (

GROUPBY (

MCBSC,

MCBSC[Kundenummer],

MCBSC[CUSTOMER_NAME],

MCBSC[MACHINE_NO],

MCBSC[METER_TYPE],

MCBSC[DESCRIPTION]

[Delta2]

),MCBSC[Anzahl_PRJ]))

Anzahl_PRJ =

CALCULATE (

COUNT(PROJECT_TASK[PROJECT_NO]),FILTER (

ARTICLE,

ARTICLE[ARTICLE_NO]= "Anfahrt"

),FILTER(PROJECT_TASK,PROJECT_TASK[RECEIVED_DATE]>=minx(ALLSELECTED('CALENDAR'),'CALENDAR'[Date].[Date]) && PROJECT_TASK[RECEIVED_DATE]<=maxx(ALLSELECTED('CALENDAR'),'CALENDAR'[Date].[Date])))

is the measure "Anzahl_PRJ" correct?

Re: sum of delta values in a date range

Anonymous — Tue, 22 Mar 2022 09:20:23 GMT

Hi fb7 ,

Could you please share a simplified pbix file(exclude sensitive info) with me? It is a little difficult to find the cause of incorrect values and get a solution base on these measure formulas...

How to upload PBI in Community

Best Regards

Re: sum of delta values in a date range

fb7 — Fri, 08 Apr 2022 08:23:28 GMT

I could the pbix file link with sharepoint share but only if you have a mail address...