Creating a Sum Measure Conditionally (Revenue vs Another Accounts)

[sreyes10]

Dear Power BI Experts,

I'm trying to create a New Measure where I have a constant value which is the Revenue. My idea is to find what is the percentage of each accounting cell against the Revenue. I have tried the next formula:

Total Revenue= CALCULATE(sum(ALS[Value]),ALL(Accounting Name)). Besides, I tried with using FILTER, but it took the whole table.

Also, I tried with "SumX".

I will how you how my data is in Excel. 

I have the same data for each year in just one column (Accounting Name, Value, Year, Month).

Do you know how can I get just the sum of the total Revenue? Thank you very much.

Service Revenue	3.796
Product Revenue	-
Reimbursable Revenue	21
TOTAL REVENUE	3.817
International Mobile	50
International Commuters	-
Home Country Commuters	-
Home Country Mobile	106
Home Country Resident Exempt	204
Home Country Resident Non-Exempt	185
GeoMobile	4
Contractors	10
TOTAL COMPENSATION	560
Materials & Supplies	249
Depreciation	335
Multiclient/Horizon Amort	-
Product cost	1
Transportation & Mobilization	249
Lease & rent	53
Office & Camp	29
Travel & Entertainment	10
Multiclient Capitalization	-
Legal and Professional fees	18
Training costs	1
Reimbursable costs	11
Other Expenses	313
Field Segment Management Cost	110
SINet	30
TOTAL FIELD COST	1.969
FIELD SEGMENT CONTRIBUTION	1.848

[vanessafvg]

@sreyes10  if you trying to get the % you need to divide the amount of a specific account into the total 

i dont think i imported in the data correctly but look at my %of total measure to show you what i think you trying to do

[sreyes10]

Hello @GvA ,

Thanks for your answer. 

I have tried that formula, but in my data I have the same data replicated for all years, this means that the table is constantly repeated. It means that I have a columns with the period for each one. 

When I used that formula, It took the whole total Revenue of all periods and not the period that I want.

[vanessafvg]

@sreyes10  if you can share your data i can see what i can do

[sreyes10]

Sure. 

This is one of my tables.

I have 15 segments with the same data, connected by date and segments. 

https://1drv.ms/x/s!AvyeYvlLZCLPnnr-55SOZdHTCR--

Thank you

[vanessafvg]

@sreyes10  would you filter on the date in a slicer?  if so you can use this

% of Total2 = CALCULATE(DIVIDE(sum(Query1[Value]), CALCULATE(sum(Query1[Value]), ALLSELECTED(Query1))))

[sreyes10]

I will filter the period and the account that I want to know the %. 

There is a way to select just the rows "TOTAL REVENUE" and sum them?

[vanessafvg]

@sreyes10  explain to me what you are trying to achieve and maybe i can be more helpful around the actual solution.

What are you trying to do and why sort thing.

[sreyes10]

Well, 

In summarize, I have financial statements for 15 segments. For those, I have 15 tables connected with date, segment and accounting Name. 

I want to find the percentage of the Xaccount / Total Revenue. In this case, if I want to find it value for period 2018, it takes the sum of the total of XAccount  and the TOTAL REVENUE of each month and get the %. 

Now I have rows that contains the TOTAL REVENUE and I want a constant variable that show me the value of TOTAL REVENUE (SUM), and if I use a filter, it doesn't affect that variable. 

Its clear that explanation?

Thanks in advance for your help!

[vanessafvg]

@sreyes10   so is it always going to be filtered at a year level?

i understand what the constant is, but where is it aggregated up to, year, year and account?  should every % always be calculated against the total for the year regardless of what fields you use to filter?

[sreyes10]

It could be a year, a month or several months. 

The % should every always calculated againts the period selected. And for example, if you select 2 o 3 fields against the total revenue, it should ((XAccount + YAccount) / Total Revenue). In that case you have to consider the period selected!

[vanessafvg]

@sreyes10  and where you do you select the year month in a slicer?

[sreyes10]

Yes, in a slicer I select the period that I want to show!

[vanessafvg]

@sreyes10 i dont know if this is what you mean but see my file attached.

if you change the slicers, it always rolls up to what you have selected in the slicers.

[sreyes10]

It seems that work, but there is a way to select just TOTAL REVENUE and sum them?

Because the "TOTAL" is just the TOTAL REVENUE.