Sales Amount grouping DAX

[bilalkhokar73]

I have data in which I have Amount , month,year, order id , item id , customer id 
I want to make sales Group on amount that

1. < 100 Dollar amount how much Dollar Sold 

2. 100-200 Dollar how much Dolalr Sold

Here is my file link - https://we.tl/t-xEOgrvHW7O

Thsi is my Desired output < i have tried this formula but giving wrong result, my desired output i pasted in image down side

#SVGrp =
var a100=CALCULATE(COUNTROWS(Sheet1),Sheet1[Amnt]<100)
var o_200=CALCULATE(COUNTROWS(Sheet1),Sheet1[Amnt]>=100 && Sheet1[Amnt]<200)
var o_300=CALCULATE(COUNTROWS(Sheet1),Sheet1[Amnt]>=200 && Sheet1[Amnt]<300)
var o_400=CALCULATE(COUNTROWS(Sheet1),Sheet1[Amnt]>=300 && Sheet1[Amnt]<=400)
var a400=CALCULATE(COUNTROWS(Sheet1),Sheet1[Amnt]>400)

return
IF(a100>0,"<100",IF(o_200>0,"100-200",IF(o_300>0,"200-300",IF(o_400>0,"300-400",IF(a400>0,"400+")))))

@amitchandak @Pragati11 @Anonymous 

[Samarth_18]

Hi @bilalkhokar73 ,

You can create a column like this:-

#SVGrp = 
switch(true(),
Sheet1[Amnt]<100,"<100",
Sheet1[Amnt]>=100 && Sheet1[Amnt]<200,"100-200",
Sheet1[Amnt]>=200 && Sheet1[Amnt]<300,"200-300",
Sheet1[Amnt]>=300 && Sheet1[Amnt]<=400,"300-400",
Sheet1[Amnt]>400,"400+"
)

Are we sure we need Amout column in value section of matrix since data difference is huge between your expected output and actual data?

Thanks,

Samarth

[bilalkhokar73]

yes  i need amount column in values and expecyted dax column in rows as i want to see 100-200 what is value i am making, 200-300 what is the value i am making , make sure you select feb month 
@Samarth_18 
this is my file  https://we.tl/t-xEOgrvHW7O

[v-janeyg-msft]

Hi, @bilalkhokar73 

Is your problem solved? @Samarth_18 's reply seems to have met your requirements. You can accept his reply as solution to help the other members find it more quickly. If you have other questions, Please describe again.

Best Regards,

Community Support Team _Janey

[bilalkhokar73]

no, still not solved

[v-janeyg-msft]

Hi @bilalkhokar73 

If it doesn't work. Can you show me what's the calculation logic of this column? sum or count? How to get this result? So we can help you.

Best Regards,

Community Support Team _Janey

[v-janeyg-msft · Accepted solution]

Hello @bilalkhokar73 

I think there is no problem with the formula, but the aggregation method of amount needs to be changed to 'count'.

Is this what you want?

Best Regards,

Community Support Team _Janey

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

[VijayP]

@bilalkhokar73  

var a_0100=CALCULATE(sum(SALESTABLE[Amnt]),Sum( SALESTABLE[Amnt]<100)
var a_100200=CALCULATE(sum(SALESTABLE[Amnt]),Sum( SALESTABLE[Amnt]<200)
var a_200300=CALCULATE(sum(SALESTABLE[Amnt]),Sum( SALESTABLE[Amnt]<300)
var a_300400=CALCULATE(sum(SALESTABLE[Amnt]),Sum( SALESTABLE[Amnt]<400)
var a_400=CALCULATE(sum(SALESTABLE[Amnt]),Sum( SALESTABLE[Amnt]>400) 

should be the approach

[VijayP]

@bilalkhokar73 

F(SELECTEDVALUE(salesgroupAmnt[Sales])=="0-100",a_0100,

After Sales why equal suymbol twice. It should be once

[bilalkhokar73]

but still wrong after making one then also

[VijayP]

@bilalkhokar73 
Create a dummy Table - Home Tab->Enter Data 
Grouping (ColumnName)
0-100 

100-200
200-300

300-400

400 >
and create a DAX like this
Switch( Selectedvalue(Grouping),
"0-100",CALCULATE(COUNTROWS(Sheet1),Sheet1[Amnt]<100)
and so on the way you have created )

and then in a table visual add Groupoing Column name and above measure

This will create the desired result!

[bilalkhokar73]

I tried this but giving this worng results
@VijayP 
#GRP =
var a_0100=CALCULATE(sum(SALESTABLE[Amnt]), SALESTABLE[Amnt]>=0 && SALESTABLE[Amnt]<100)
var a_100200=CALCULATE(sum(SALESTABLE[Amnt]),SALESTABLE[Amnt]>=100 && SALESTABLE[Amnt]<200)
var a_200300=CALCULATE(sum(SALESTABLE[Amnt]),SALESTABLE[Amnt]>=200 && SALESTABLE[Amnt]<300)
var a_300400=CALCULATE(sum(SALESTABLE[Amnt]),SALESTABLE[Amnt]>=300 && SALESTABLE[Amnt]<=400)
var a_400=CALCULATE(sum(SALESTABLE[Amnt]),SALESTABLE[Amnt]>400)
return
IF(SELECTEDVALUE(salesgroupAmnt[Sales])=="0-100",a_0100,
if(SELECTEDVALUE(salesgroupAmnt[Sales])=="100-200",a_100200,
IF(SELECTEDVALUE(salesgroupAmnt[Sales])=="200-300",a_200300,
IF(SELECTEDVALUE(salesgroupAmnt[Sales])=="300-400",a_300400,
IF(SELECTEDVALUE(salesgroupAmnt[Sales])=="400+",a_400 ) ))))

[bilalkhokar73]

 https://we.tl/t-xEOgrvHW7O