Dear all,
I hope this message finds you well. I am currently encountering an issue while using Looker and would appreciate your assistance. Please refer to the example below, which illustrates the problem I am facing and the desired outcome.
I am working with a dataset that includes the following information:
| account_type |
account_num |
account_name |
amount |
| Revenue |
4110 |
stream_1 |
2000 |
| Revenue |
4120 |
stream_2 |
2500 |
| Revenue |
4130 |
stream_3 |
5000 |
| Revenue |
4140 |
stream_4 |
2600 |
| Operating_Exp |
6110 |
general & admin |
2000 |
| Operating_Exp |
6120 |
marketing & advertisement |
1000 |
| Operating_Exp |
6130 |
payroll |
2500 |
| Operating_Exp |
6140 |
other expenses |
500 |
My objective is to create a visual representation of the Operating Expenses table, including a new column/field that displays "Operating Exp" as a percentage of the "Total Revenue." I would like to highlight that I have already established "total_revenue" and "total_operating_expenses" as measures in my Look ML model.
So the required output table would be as follow:
| account_name |
total_operating_expenses |
% of total_revenue |
| general & admin |
2000 |
16.5% |
| marketing & advertisement |
1000 |
8.3% |
| payroll |
2500 |
20.7% |
| other expenses |
500 |
4.1% |
| Totals |
6000 |
49.6% |
To achieve this, I have added the relevant dimensions and measures to the table. Next, I created a filter for the table to only display operating expenses, using the condition "account_type = Operating_Exp." Subsequently, I created the "% of total_revenue" field as a table calculation by applying the following custom table expression:
${total_operating_expenses}/${total_revenue:total}
However, the issue I am encountering lies in the fact that I am unsure how to remove the filter from the "Total Revenue" portion of the above expression. By doing so, the account_type filter for revenue would be eliminated, allowing for the calculation of the percentage. In Power BI, we have functions such as "ALL" and "REMOVEFILTER" that facilitate achieving this outcome.
I kindly request your guidance on how whether it is achievable by following my approach and we can remove the filter for the calculation or if there are any alternative approaches to resolve this matter. Thank you in advance for your kind support.
Best regards, Rashid Anwar
LinkedIn
Twitter
