sum of delta values in a date range

[fb7]

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

[Anonymous]

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

[fb7]

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.

[Anonymous]

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

[fb7]

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?

[Anonymous]

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

[fb7]

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

[littlemojopuppy]

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.

[fb7]

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"

[littlemojopuppy]

@fb7 those would be measures that correspond to these