Subtotal by fields (maybe Aggr)

[shane_spencer]

I'm trying to get a % of "VRN Check" for each scenario. To do that I want a Count of Records by [Lock Local Period], [TDW Legal Entity Code] and [TDW Amz Reg Jurisiction] only (exluding the [VRN Check] field) so I can divide the Count of Records by that. 

So in the below example the new column should be the 1359 for Dec-2024 ACCI AB for both the first 2 rows.

[Bhushan_Mahajan]

@shane_spencer Aggr by Local period and use total function in measure

[shane_spencer]

I'm getting closer but I have an issue where it is putting the count per scenario in the first row but not the second. If you look at the below example I have kind of got the results using Set Analysis (top visual) but have had to remove the [VRN Check] field and put the %ages over 3 columns, but what I want to do is have the layout in the bottom visual but as you can see the circled cell is blank when it also should be 62 like the row above (i.e. 26 + 26). Hopefully you can see the formula I used in the labels.

Aggr(Count([Lock Local Period]) ,[Lock Local Period],[TDW Legal Entity Code],[TDW Amz Reg Jurisdiction])

 

[Bhushan_Mahajan]

@shane_spencer Better do it in backend. Refer below script.

ABC:
Load * Inline [
Period,Code,Juri,VRN,Count
Dec-24,A,B,Missing,1354
Dec-24,A,B,Incorrect,4
Jan-24,A,B,Missing,1176
Jan-24,A,B,Incorrect,8
];

left join
CDE:
Load Period, Sum(Count) as Total
Resident ABC
group by Period;
Exit Script;

[shane_spencer]

Thanks but I'm looking to do this in the visuals if that is possible. Also it's not Grouping up by period but 3 fields [Lock Local Period],[TDW Legal Entity Code] and [TDW Amz Reg Jurisdiction].

[Bhushan_Mahajan]

@shane_spencer I think for more than one identifier, it will not work in straight table. You need to work it out in backend. 

[MatheusC · Accepted solution]

Hi, @shane_spencer 

With the total<> qualifier, like this:

Count( Total <[Lock Local Period],[TDW Legal Entity Code],[TDW Amz Reg Jurisdiction]> [Lock Local Period])

- Regards, Matheus 

[Bhushan_Mahajan]

@MatheusC Yes. It is right option to tackle in front end. Great.

[shane_spencer]

@MatheusC that does not give me the expected value. For example if you look at the highlighted it shows 1752 instead of 62

Update: if I select a value from [Lock Local Period] it gives me the right value but isn't a solution in itself.

Update2: I belatedly realised that the [Lock Local Period] was not the field from the data but a calculated field. That's the trouble with supporting something someone else has built!