topic Re: Percentage of grand total measure in DAX Commands and Tips

Percentage of grand total measure

Anonymous — Thu, 15 Dec 2022 01:17:26 GMT

I am wanting to write a measure that gives me the percentage of grand total (of a row item), issue is that this is using other measures to calculate.

What I'm trying to do is check how much each department is spending of budget per month from their total budget spend.
Ideally I want to get the columns measure grand total and then divide each department's spend by it.
The "spend" is currently a measure, so it seems I can't use:

Measure = CALCULATE(SUM('Table1'[Spend])

How would I go about returning the total sum of a measure (e.g., the total spend of the departments)?

Re: Percentage of grand total measure

Mahesh0016 — Thu, 15 Dec 2022 05:45:19 GMT

% Dept_budget =

VAR V1 = CALCULATE([Measure],ALLSELECTED('Table'[department]))

RETURN

DIVIDE([Measure],V1,0)

Re: Percentage of grand total measure

Anonymous — Thu, 15 Dec 2022 20:59:46 GMT

For some reason it returns 100% for every row. Not sure why but it's calculating at the row level, not the column level.

I've tried SUMX - does the same thing row level.

Sum doesn't work at all. Must be some restrictions to my database connection? (is live)

Re: Percentage of grand total measure

Anonymous — Mon, 19 Dec 2022 06:32:03 GMT

Hi Anonymous,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: Percentage of grand total measure

Mahesh0016 — Mon, 19 Dec 2022 06:33:42 GMT

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.