Ideas for Calculations

[KIL2022]

Hello,

im Lookig for folling sollution

Example data for first Customer

Z0	Z1	Costumer	Item	Date	Qty
E	E-Nord	A	Artikel 1234	28.09	3
E	E-Nord	A	Artikel 1234	02.10	10
E	E-Nord	A	Artikel 4321	18.09	2

If I switch the calculation to "Continuous", i.e. for Artikle 4321, although there were no entries in period 01.11, 01.12, etc., I assume that the Qty remains equal to 2, everything is also calculated correctly.

However, if I drill up to Customer or Z1, the values are not added correctly.

I also know why, because I don't add up the values beforehand, but calculate them using the formula MAXX

CALCULATE(MAXX(FILTER(MockUPDATEN.....

I try it via the sums, with: CALCULATE(SUM(MockUPDATEN[Test2])....
It does not pull in the MAXX data per Item, but all data, here:
Correct for Customer A and 01.11 - 10+2 = 12
Here: 10+3+2 =15
I'm at a loss as to how I can solve it, i.e. get the correct summation...
In principle, I would like to
Sum(Grupby (ITEM, Maxx(Date))

Thanks

[v-tangjie-msft · Accepted solution]

Hi @KIL2022 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a table.

Table 2 = VALUES('Table'[Date])

(3) We can create measures.

Measure = 
var _a= MAXX(FILTER(ALL('Table 2'),'Table 2'[Date]<MAX('Table 2'[Date])),[Date])
var _b=CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'), 'Table'[Z0]=MAX('Table'[Z0]) && 'Table'[Z1]=MAX('Table'[Z1]) && 'Table'[Costumer]=MAX('Table'[Costumer]) && 'Table'[Item]=MAX('Table'[Item]) && [Date]=_a ))
var _c=CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'), 'Table'[Z0]=MAX('Table'[Z0]) && 'Table'[Z1]=MAX('Table'[Z1]) && 'Table'[Costumer]=MAX('Table'[Costumer]) && 'Table'[Item]=MAX('Table'[Item]) && [Date]=MAX('Table 2'[Date] )))
return IF(_c=BLANK(),_b,_c)

Measure 2 = IF(ISINSCOPE('Table'[Costumer]),SUMX(VALUES('Table'[Item]),[Measure]))

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[lbendlin]

There is a GROUPBY function that you could use for this

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...