topic Re: Column Subtotal not working in DAX Commands and Tips

Column Subtotal not working

Amitkr174 — Fri, 17 Nov 2023 09:37:40 GMT

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.

Re: Column Subtotal not working

lukiz84 — Fri, 17 Nov 2023 08:33:19 GMT

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]'

Re: Column Subtotal not working

Amitkr174 — Fri, 17 Nov 2023 09:28:11 GMT

Sorry, it's a calculated column.

Re: Column Subtotal not working

lukiz84 — Fri, 17 Nov 2023 09:42:44 GMT

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

Re: Column Subtotal not working

Amitkr174 — Fri, 17 Nov 2023 10:04:52 GMT

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

Re: Column Subtotal not working

lukiz84 — Fri, 17 Nov 2023 10:10:47 GMT

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.

Re: Column Subtotal not working

Amitkr174 — Fri, 17 Nov 2023 10:23:12 GMT

so how i solve this issue. Please advise.

Re: Column Subtotal not working

lukiz84 — Fri, 17 Nov 2023 10:29:30 GMT

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

Re: Column Subtotal not working

Amitkr174 — Fri, 17 Nov 2023 11:29:48 GMT

yes, you are right

Re: Column Subtotal not working

lukiz84 — Sat, 18 Nov 2023 08:51:59 GMT

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