Using the Sum Total of a column as part of a new Measure

DAX DAX Calculations
1

Hi,

It’s been awhile, but this forum has been extremely helpful in the past. I feel it is important to understand/solve this scenario more generically in Power BI DAX, but am adding the PBIX and source data files used.

Please refer to the attachments.

  1. PBIX file (added after 15 views produced no replies)
  2. Screenshot utilizing selections & filters to get to the desired projects per group where the 'Contract Amt Expected sum total is to be leveraged.
    3 & 4) simple source data files

Percent of Total PBIX for Enterprise DNA.pbix (122.0 KB)

Table visual for eDNA
Table visual for eDNA1039×362 34.6 KB

data.csv (237.0 KB)
Teaming Table.csv (8.9 KB)

Amongst various user-driven record selection criteria, the “-CPP filter” is applied. The table includes columns for the same “Contract Amt Expected” column; one as its sum and the other as the % of Grand Total sum.

The objective can be accomplished in three ways:

  1. How can I use the %GT Sum of Contract Amt Expected column as part of a new Measure formula (ex. by row, multiply the %GT and a different column)?
  2. How can I create a measure which replicates the %GT Sum of Contract Amt Expected column?
  3. How can I make/use the Total of the “Sum of Contract Amt Expected” column ($177,574,305.28) into its own measure to apply to every row in the table visual based on selected records? Visually, that’s a table visual column added here with $177,574,305.28 in every row; preferably with that same amount as its total.

I get lost in the jungle of ALL, ALLSELECTED, VALUES, FILTER, etc. functions trying to achieve this without truly understanding the concepts.

I appreciate the help.

Kind regards,
Kevin

2

Hello, @kkieger

Based on what i understood from youre ask, here is the solution thata should answer all 3 questions. you wanted to answer

image
image1076×294 24.2 KB

For your Question 1 & 2, the Answer is this measure :

**%of_GT_ContrctAmtExpected** = DIVIDE([Sum_ContractAmt_Expected],[Total_Contract_Amt_Expected],"-")

and If i understood your ask correctly, then, answer to your Question 3 should be this measure:

**Total_Contract_Amt_Expected** = CALCULATE(SUM(data[Contract Amt Expected]), REMOVEFILTERS(data[Project Number]))

I hope this helps.

I have attached a working file for your reference.
Percent of Total PBIX for Enterprise DNA_sol_Japjeet.pbix (124.6 KB)

Regards,
Japjeet

2 Likes
3

Thanks, Japjeet @jsodhi

You definitely solved it. I noticed in your pbix that when I change Groups ((from Chicago Operations to Milwaukee Operations), the resulting table includes a second Company value… abd the ‘Total_’ measure has separate values per company. Thus, I included “Company” in the REMOVEFILTERS clause. This would be true of any other dimensional column added to the table visual whose values differ.

Great work.

-Kevin

1 Like