cancel
Showing results for
Did you mean:
BusinessAnalyst Regular Visitor

## Calculate function turned wrong

Hi everyone,

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

4 REPLIES 4
Highlighted Super User

## Re: Calculate function turned wrong

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.

Lima - Peru

Proud to be a Datanaut! Super User

## Re: Calculate function turned wrong

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. GROUPBY CONDITIONAL COLUMN

Thanks & Regards,
Bhavesh

Love the Self Service BI.
BusinessAnalyst Regular Visitor

## Re: Calculate function turned wrong

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
Moderator

## Re: Calculate function turned wrong

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.

`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"))` 