cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BusinessAnalyst Regular Visitor
Regular Visitor

Calculate function turned wrong

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

 

error.JPG

 

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
Super User
Super User

Re: Calculate function turned wrong

@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.




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
Super User
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.

BA Grouping.PNGGROUPBYBA Grouping2.PNGCONDITIONAL COLUMN

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
BusinessAnalyst Regular Visitor
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
Moderator v-sihou-msft
Moderator

Re: Calculate function turned wrong

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

999.PNG