Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jtran
Regular Visitor

Creation of KPI/Gauge that adjusts based on 2 slicers

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. scheme.png

 

Data sets appear like so;

Target table

 

 

T.PNG

 

Calendar table - using CALENDAR function to returns dates from 01/01/2017 to 31/12/2017

 

Revenue tableR.PNG

 

 

Expenses tableE.PNG

 

 

 

 

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

4 REPLIES 4
v-qiuyu-msft
Community Support
Community Support

 

 

 

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-qiuyu-msft

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:

 

e1.PNG

 

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")))

 

 

e2.PNGe3.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-qiuyu-msft

 

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

PBIX file

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.