Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
Still new to Power BI but slowly progressing as I build out the solution. I'm come to a standstill regarding this issue on how to develop a KPI, Gauge or simple % measurement on progress to target, profit and margin calculations.
Attached below is the relationship view I have.
Data sets appear like so;
Target table
Calendar table - using CALENDAR function to returns dates from 01/01/2017 to 31/12/2017
Revenue table
Expenses table
Based on the Account and Date period (most cases would be YTD) selected in the slicer the report would show progress to target, profit and margin calculations (ideally in visualizations such as KPI/Gauge/Card visual)
- 1. Progress to target: Revenue generated/Target
- 2. Profit: Fee - Cost
- 2A. Allocate Business costs across all active accounts
- 3. Margin: Profit / Revenue
I was able to get the aggregate profit and margin figures by transforming the date field to beginning of month, then groupby date to align the revenue against costs in another query before creating calculated columns using M but this method doesnt factor the Account slicer to adjust and breakdown when selected.
Any help or advice would be appreciated.
Thanks
What I need is calculations for the following based on the slicers present which show Account and a relative Date slicer.
Based on the Account and Date period (most cases would be YTD) selected in the slicer the report would show progress to target, profit and margin calculations
- (KPI/Gauge) Progress to target: Revenue generated for the selected account/Target
- (Card visual) Profit: Fee - Cost
- (Card visual) Margin: Profit / Revenue
Hi @jtran,
How we can get "Revenue" data? From your description, it seems Revenue is a table. So what is it in "Progress to target: Revenue generated for the selected account/Target " and "Margin: Profit / Revenue"?
Please share some sample data and clarify the corresponding desired results for our analysis.
Best Regards,
QiuyunYu
Thanks for reaching out. I've adjusted the post to include sample data to clear things up.
I have no issue getting this done in excel but getting it to work with slicers proves to be tricky.
To clarify in the following scenarios I would get the following with Account slicer showing Client A, Client B, Client C and Relative Date Slicer;
1. Assuming Client A and This Year is selected; YTD revenue would be $9250 for WON business , Target being $106,000. What I need to show is YTD revenue/Target = 8.73% of target achieved.
2. Similar to scenario 1 but subtract Cost to generate a profit figure.
i.e Client B and This Year is selected; YTD revenue is $7000, Cost is $24000. Resulting in a profit of -$17,000
2A. An additional thing I need to do is allocate the costs listed as Business and distribute them to the 3 accounts.
i.e YTD business cost ($96000) / 3 = $32000. This figure would actually supplemented to the cost figures in 2, driving the profit result much lower.
3. Using figures generated by 2 return output of margin
i.e -$17000 / $7000 = - 242.85% margin
This logic would adjust as the Account slicer is selected for each Account and Date set.
Hi @jtran,
Please add a new table which contains distinct account name, then build new relationships like below:
Create measures below:
YTD revenue/Target = CALCULATE(SUM(Revenue[Fee]), FILTER(ALLSELECTED('Revenue'),'Revenue'[Status]="WON"))/SUM('Target'[Target])
Profit = CALCULATE(SUM(Revenue[Fee]), FILTER(ALLSELECTED('Revenue'),'Revenue'[Status]="WON"))-SUM('Expenses'[Cost])-DIVIDE(CALCULATE(SUM('Expenses'[Cost]),FILTER(ALL('Expenses'),'Expenses'[Account]="Business")),3)
Margin = DIVIDE(CALCULATE(SUM(Revenue[Fee]), FILTER(ALLSELECTED('Revenue'),'Revenue'[Status]="WON"))-SUM('Expenses'[Cost]),CALCULATE(SUM(Revenue[Fee]), FILTER(ALLSELECTED('Revenue'),'Revenue'[Status]="WON")))
Best Regards,
Qiuyun Yu
The YTD Revenue/Target figure appears to be correct and working as the account slicer is selected but the Profit and Margin calculations dont solve what im looking for.
I've made some adjustments to the pbix you sent and connected the calendar to the date fields to Revenue and Expenses.
Some additions I made was to add a line/bar chart (visually present revenue as bars and costs as the line graph) and waterfall chart (display profit increase or decrease per month) in addition to the card visualizations that provide a static measure.
Link below
You will notice in the Expenses table there are several costs associated to Business which need to be distributed to Client A, Client B and Client C costs (not measured as its own value in the Account slicer).
The scenario being; Names listed as Business are admin/management costs that dont generate revenue and are to be offset by accounts which generate fees such as Client A, Client B and Client C.
- The complexity arises when new clients are signed requiring adjustment of the distribution of costs according to the number of clients at that point in time. i.e Client D joins in October 2017 which requires costs for Client D + 1/4 of Business costs when calculating profit and margin but July to Sept costs are distributed only to Clients A, B, C for those months
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |