Calculate function turned wrong

[BusinessAnalyst]

Hi everyone, 

Can you please help me to check why this Calculate function went wrong:

Week Amount = CALCULATE(sum(Merge[TotalAmount]),filter('Merge',Merge[YearWeeknum of PurchaseDay]))

Type = if([Week Amount]>9000,"high",if([Week Amount]>3000,"medium",if([Week Amount]>10,"low")))

Low = CALCULATE(counta(Merge[ID]),filter('Merge',[Type]="low"))

There are only 4 "low" in the measure "Type" but when in card visual, it shows there are 241 "low". 

Please check the data here: test

One more question, is there anyway to make "Type" able to slicer visual?

I look forward to learning from you. 

Many thanks and regards,

Cindy

[BhaveshPatel]

Once again I am back to provide you a solution.

Instead of calculating using DAX, this time again Query Editor was handy to use.

I have grouped the amount by IDs and then created a conditional column to achieve results. You can also use classification column as slicer( High, Medium, Low). Please see the screenshots.

GROUPBYCONDITIONAL COLUMN

[BusinessAnalyst]

Hi experts,

Thanks for your contributions but it wasn't what I meant to create.

When grouped by ID, it sums all purchase amount while I wish to sum the purchase amount by week. So the types are labeled due to weekly purchase amount

[v-sihou-msft]

@BusinessAnalyst

Since you need to get the count of the ID baesd on the Week Amount calculation aggregated on ID, Week Number and Week Number of Registration, you should have this calculation group on ID, Week Number and Week Number of Registration. In this scenario, you calculation aggregate on incorrect level so that ID will be count multiple times within current context scope.

Please change your Week Amount measure:

Week Amount = CALCULATE(sum(Merge[TotalAmount]),ALLEXCEPT(Merge,Merge[ID],Merge[Week number],Merge[Week Number of Registration]))

Also you need to distinctcount your ID:

Low = CALCULATE(DISTINCTCOUNT(Merge[ID]),filter('Merge',[Type]="low"))

[Vvelarde]

@BusinessAnalyst

if you put total amount in the table you'll see the 241 rows.

the measure sales amount is suming the rows with equal id and weeknumber.