Help creating a table with summary of sales bookings to target by license type

[mabegg]

I am trying to create a visual inpower bi to show sales bookings by type by business unit by quarter against target.

The sales data is pulled from salesforce opportunity table.

I created a table with targets, the values are manual entered and only exist in the data model and not the data source.

BU exists in the salesforce table opportunity table Opportunity[BU], I could make a many to many relationship from the target table to opportunity table but haven't done so yet.

I need to create 4 calculated columns that show the sum of bookings closed in each partuclar quarter. If a quarter is already closed they would be actuals based on closed date, if we are in teh current quarter or in the futue they would be a forecast as those opportunites would be open!

Subscription bookings are based on opportunity type subscription for Opportunity[License Revenue] and License bookings for opportunity type Perpertuual for Opportunity[License Revenue]. Services comes from Opportunity[Services Revenue] and Support from Opportunity[Support Revenue].

Ultimately trying to product a visual similar to the excel table below

I hope I have managed to explain this as it is quite complicated problem I am trying to solve and maybe making the table for targets was the wrong place to start

[v-yulgu-msft]

Hi @mabegg,

Please post some dummy data of source tables.

I need to create 4 calculated columns that show the sum of bookings closed in each partuclar quarter. If a quarter is already closed they would be actuals based on closed date, if we are in teh current quarter or in the futue they would be a forecast as those opportunites would be open!

How to determine whether a quarter is closed or not? Besides, please show us your desired output with examples? Show some data in the above excel table. 

Regards,

Yuliana Gu

[mabegg]

Ok think I partially solved the issue, data is coming back as how I would expect for the measures when the matrix visual is drilled down.

Essentially used a few if statements to check the type of license, used TREATAS to filter the calculation by Business Unit and depending on the condition evaluated the appropriate sum. I repeated this for each quarter I wanted to calculate changing the value of fiscal which is a column in the salesforce table. Additional check on the Software License ACV column to check whether it was subscription of perpetual but the Software_Term_c column. I probably did not do a great job of fully explaining the problem and this solution is probably a little ugly but seems to get the job done for the sample set I had.

I would have posted dummy data but that will take some time to extract and cleanse

Q1 =
if(CONTAINS('BU Target','BU Target'[Type], "License"),
calculate(
sum(Opportunity[Software License ACV]),
TREATAS(VALUES(Opportunity[Legacy_Brand__c]),'BU Target'[BU]),
filter(Opportunity,Opportunity[Fiscal] = "2018 1" && Opportunity[Software_Term__c] = "Perpetual")
),if(CONTAINS('BU Target','BU Target'[Type], "Subscription"),
calculate(
sum(Opportunity[Software License ACV]),
TREATAS(VALUES(Opportunity[Legacy_Brand__c]),'BU Target'[BU]),
filter(Opportunity,Opportunity[Fiscal] = "2018 1" && Opportunity[Software_Term__c] = "Subscription")
),
if(CONTAINS('BU Target','BU Target'[Type], "Software Updates & Support"),
calculate(
sum(Opportunity[Support_Revenue__c]),
TREATAS(VALUES(Opportunity[Legacy_Brand__c]),'BU Target'[BU]),
filter(Opportunity,Opportunity[Fiscal] = "2018 1")
),if(CONTAINS('BU Target','BU Target'[Type], "Professional Services"),
calculate(
sum(Opportunity[Services Revenue ACV]),
TREATAS(VALUES(Opportunity[Legacy_Brand__c]),'BU Target'[BU]),
filter(Opportunity,Opportunity[Fiscal] = "2018 1")
)))))

Problem is when I drill up the targets roll up correctly as they were manually entered into the table but the measures do not

 

The forecast 2018 is a measure - Forecast 2018 = [Q1]+[Q2]+[Q3]+[Q4] which sums up the measures with the DAX above

Target 2018 is a measure - Target 2018 = sum('BU Target'[Q1 Target])+sum('BU Target'[Q2 Target])+SUM('BU Target'[Q3 Target])+sum('BU Target'[Q4 Target])

Variance 2018 is a measure - Variance 2018 = [Q1 Variance]+[Q2 Variance]+[Q3 Variance]+[Q4 Variance]

Variance is calculate as Q1 Variance = [Q1]-SUM('BU Target'[Q1 Target])

This totalling issue when drilling up I have now solved with the following:-

Forecast 2018 = sumx(values('BU Target'),[Q1]+[Q2]+[Q3]+[Q4])

Variance 2018 = sumx(values('BU Target'),[Q1 Variance]+[Q2 Variance]+[Q3 Variance]+[Q4 Variance])