Hi Saad. Is [Revenue Sumbmission Compliance] a calculated field that is an aggregate?
Could you post some sample data in a twbx file? This will help in understanding which of your measures are aggregated.
Thanks for writing back. Attached please find the sample data that I'm working off with. The data in real time environment is coming from five queries (including host of other metrics as well). That is why I have to factor in the F'cast Compliance Flag from each of the query.
- When Project Class is 'Subscription', I just take it as Y, regardless of how many times does that project ID appear in the queries.
- When Project Class is 'Fixed' or 'Transaction', my first query is the decider. It either gives me a 'Yes' or 'No', based on the defined criteria. If the Project ID appears in the subsequent queries, it is taken as a NULL.
- For Subscription projects, it is simple. Sum up all the 'Yes' (which I assign the integer value 1) and divide by the count of Project IDs (basically we're always going to want to have 100%)
- For Fixed / Transaction projects, we need to just slice in the data from first query. In other words determine if it is a Yes (1) or No (0), and divide it with the distinct count (COUNTD) of Project IDs.
Let me know if you need any other clarity about the data.
Thanks for your time once again,
I'm not sure if I have this right, but Calculation1 in the attached workbook is similar to the formula described above.
Hope this helps a bit!
That's correct Tracy. Thanks a lot!