topic Re: Trying to get a calculation from 2 different column totals.... in DAX Commands and Tips

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

mikekolba1 — Sat, 05 Oct 2024 23:35:39 GMT

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.

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

mikekolba1 — Sun, 06 Oct 2024 00:07:25 GMT

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

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

dharmendars007 — Sun, 06 Oct 2024 18:57:54 GMT

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

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

mikekolba1 — Mon, 07 Oct 2024 13:39:41 GMT

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 %

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

mikekolba1 — Mon, 07 Oct 2024 14:57:33 GMT

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