Column Subtotal not working

[Amitkr174]

Hi,

I have created a Deal_Band Calculated Column, which I am putting it in a Matrix table, but the total is getting repeated.

New_Deal_Band = IF('DRID-SolutionMapping'[Net_ASV]>5000000,"5M+",

                IF('DRID-SolutionMapping'[Net_ASV]>=3000000 && 'DRID-SolutionMapping'[Net_ASV]<=5000000,"$3M-$5M",

                    IF('DRID-SolutionMapping'[Net_ASV]>=1000000 && 'DRID-SolutionMapping'[Net_ASV]<3000000,"$1M-$3M",

                        IF('DRID-SolutionMapping'[Net_ASV]>=500000 && 'DRID-SolutionMapping'[Net_ASV]<1000000,"$500K-1M",

                            IF('DRID-SolutionMapping'[Net_ASV]>=250000 && 'DRID-SolutionMapping'[Net_ASV]<500000,"$250K-$500K",

                                IF('DRID-SolutionMapping'[Net_ASV]>=100000 && 'DRID-SolutionMapping'[Net_ASV]<250000,"$100K-$250K",

                                     IF('DRID-SolutionMapping'[Net_ASV]<100000, "<100K", BLANK())))))))

 One Case is appearing is all the Deal Bands., like in the below case $15M, getting showing up  in all the DealBands. Please advise on this.

[Amitkr174]

so how i solve this issue. Please advise.

[lukiz84]

not so easy... because for each Entry in "Top Accounts" you can have MULTIPLE (1:m) values for New_Deal_Band

I don't think that's what you want... ?

[Amitkr174]

yes, you are right

[lukiz84]

so then you first have to change your datamodel - what do you want to achieve?

[lukiz84]

according to your picture, Top_Accounts filters DRID-Solutionmapping, not the other way around (1:m) - so when you add New_Deal_Band from DRID-SolutionMapping to your matrix, it doesn't know how to filter the data, hence showing the same value everywhere.

[Amitkr174]

here is the data model connected with DR ID (Many to one)

[lukiz84]

we need more insight in your data model. can you show some tables you use for your matrix and the relationships between?

[lukiz84]

You sure it's a measure? Doesn't make sense, as in a measure there would be no single value for 'DRID-SolutionMapping[Net_ASV]'

[Amitkr174]

Sorry, it's a calculated column.