I have a dataset which contains 20 columns .1st Column Contains Application Name , 2nd Column Contains Total charges and rest 18 column Contains Claims Platform Name. The problem which I am facing is related to current structure of the data. Here, the claim Charges are at the top of Claim Platform Header . Please Refer the below Image for better Understanding.
As you can see in my data, the yellow cell contains the total charges which are present at the top of the Claim platform Header. I need to capture this value if a platform has “Y” .
Let’s take an example .We have Long App name “A” which contributes $1000. Now, three platforms X, Z and A1(Highlighted in red) are using this APP.
So first we count the Number of “Y” for each app and add their individual charges. Say for App A we have Claim platform X which uses 1000, claim platform Z which uses 3000 and, claim platform A1 which uses 4000. So total charges will be 1000+3000+4000=8000.
Now lets take the contribution of Each platform by taking the Ratio .So formula would be
Claim Platform X = (Each Platform Contribution / Sum of All the charges which shows “Y”) * Total Charges
My final result will look like the below image.
Please let me know your thoughts how I can achieve this in Tableau. or If not inside tableau what sort of data preparation I need to do for getting the above mentioned result.
With excel formula I generated the below data-set but same i wanna achieve inside Tableau