Trying to get a calculation from 2 different column totals....

[mikekolba1]

I have two columns that are calculating row values  correctly with these formulas but the latter Site Target GP was not calculating the total properly so i had to modify 

Original

Current Revenue = CALCULATE(
SUM(Query1[Column2]),
FILTER(Query1,SEARCH("Current",Query1[Source.Name],,0)),
FILTER(Query1,Query1[Column1]="Revenue")
)

and

Site Target GP = [Current Revenue]*[Target GP Margin %]

The above was modified to this in order to calcuate the total value correctly, which it does

Site Target GP TotalCalc = IF(HASONEFILTER(Query1[Site]),[Site Target GP],SUMX(VALUES(Query1[Site]),[Site Target GP]))

Now I have another calculation i want to do for Site Target GP % based on the "totals" of these 2 calculations

I want to calculate

Site Target GP% = [Site Target GP]/[Current Revenue]

and individual sites get calculated correctly for this but

but for the "Total"of the Site Target GP% calculation I need to do something simliar to what i did with the Site Target GP calculation and override the "total" 

The formula needs to be the value of  Total "Current Reveue" (of all sites) which totals correctly for all sites with the first formula and the default power BI summation  divided by  the total value I'm obtaining via  Site Target GP TotalCalc = IF(HASONEFILTER(Query1[Site]),[Site Target GP],SUMX(VALUES(Query1[Site]),[Site Target GP])) 

How's the best way to achieve this? These are all "measures' also. 

[dharmendars007 · Accepted solution]

Hello @mikekolba1 

To calculate the overall total (denoted as "X" in your example), you need to do a weighted average calculation using Current Revenue as the weight.

Instead of directly summing up the percentages, you can use this formula to calculate the total Site Target GP % based on the weighted average

SiteTargetGP_TotalCalc =
IF(
HASONEFILTER(Query1[Site]),[Site Target GP %], -- Use the individual site value
DIVIDE(
SUMX(VALUES(Query1[Site]), [Site Target GP] * [Current Revenue]),

SUMX(VALUES(Query1[Site]), [Current Revenue])))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[dharmendars007 · Accepted solution]

Hello @mikekolba1 

To calculate the overall total (denoted as "X" in your example), you need to do a weighted average calculation using Current Revenue as the weight.

Instead of directly summing up the percentages, you can use this formula to calculate the total Site Target GP % based on the weighted average

SiteTargetGP_TotalCalc =
IF(
HASONEFILTER(Query1[Site]),[Site Target GP %], -- Use the individual site value
DIVIDE(
SUMX(VALUES(Query1[Site]), [Site Target GP] * [Current Revenue]),

SUMX(VALUES(Query1[Site]), [Current Revenue])))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[mikekolba1 · Accepted solution]

Doing this though got me the answer i needed so giving you credit as it led me to it

SiteTargetGP_TotalCalc =
IF(
HASONEFILTER(Query1[Site]),[Site Target GP %], -- Use the individual site value
DIVIDE(
SUMX(VALUES(Query1[Site]), [Site Target GP]),

SUMX(VALUES(Query1[Site]), [Current Revenue])))

[mikekolba1]

Thanks Dharmendra- In this case , not quite.  the individual site % are correct but the overall "total" % calculation is incorrect and actually way higher-

so for example

My site target GP total calc number is like 400,000

My Current Revenue total number is like 2,600,000 

it should calculate about 15% as an example.  using what you provided it calculated around 95,000 % 

[mikekolba1]

some example data to illustrate

site	Current Revenue	Site Target GP	Site Target GP TotalCalc	Site Target GP%	Site Target GP% TotalCalc
a	50	5	5	10	10
b	55	8	8	11	11
c	50	7	7	9	9
d	30	4	4	5	5
Total	185	24	6	35	X

Trying to get the (X) calculated value from the 185/6  (30.333)  using the measure information above