topic Re: Calculation Issue in DAX Commands and Tips

Calculation Issue

wshanewood1 — Fri, 15 May 2020 17:32:18 GMT

This is my first time using this site, and I am rather new to DAX. What I am trying to do seems simple, but for whatever reason, I can't get it to work out. I am trying to multiple a percentage by another percentage. For exampe I have [Cat. Grade] of 99.67, I want to multiply that by [% WEIGHTED] of 2.31%. Instead of getting .023, if am getting .99675.

The formula I am using is Final Calc:=calculate([Cat. Grade]*[%WEIGHTED],filter(GRADES,[WEIGHTED]="WEIGHTED"))

WEIGHTED	Category	Cat. Grade	%WEIGHTED	Final Calc
UNWEIGHTED	Activity Gains	82.00%
UNWEIGHTED	Activity Losses	95.00%
UNWEIGHTED	Entitlements	90.09%
WEIGHTED	Allotments	99.67%	2.31%	0.99675
WEIGHTED	Collections	99.21%	0.10%	0.99211
WEIGHTED	Deductions	97.97%	29.79%	0.97969
WEIGHTED	Events	94.43%	9.19%	0.94426
WEIGHTED	Extensions	100.00%	3.25%	1.00000
WEIGHTED	Held Pay	94.00%	0.03%	0.94000

Re: Calculation Issue

Greg_Deckler — Fri, 15 May 2020 17:11:41 GMT

Hard to be certain with the information provided. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Calculation Issue

Greg_Deckler — Fri, 15 May 2020 19:59:18 GMT

Is what you are display source data or is it a table/matrix visualization representation of your data?

If it is a table/matrix visualization, are [Cat. Grade] and [%WEIGHTED] columns or measures. If measures, what are their formulas?

Re: Calculation Issue

Anonymous — Sat, 16 May 2020 14:13:15 GMT

Hi wshanewood1 ,

Please create the following measure.

Multiple = CALCULATE(SUMX(Table10, Table10[Cat. Grade]*Table10[%WEIGHTED]))

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

Re: Calculation Issue

wshanewood1 — Wed, 20 May 2020 16:12:07 GMT

I appreciate the help, but for whatever reason it didn't work out for me (I pasted image below). Maybe its something wrong with the other formulas I am using.

%WEIGHTED:=([WEIGHTED TOTALN]/[TOTAL UNWEIGHTED])

WEIGHTED TOTALN:=calculate([Total Transactions],filter(GRADES,[WEIGHTED]="WEIGHTED"))

TOTAL UNWEIGHTED:=CALCULATE(SUM([WEIGHTED TOTAL]),ALL(Grades[Category]),ALL(Grades[PAY / PERS]))

Cat. Grade:=if(CONTAINS(Grades,Grades[PAY / PERS],"PERS"),[PERS CALC],[PAY CALC])

(and i have a calculation for each grade.)

Re: Calculation Issue

Anonymous — Wed, 20 May 2020 17:29:02 GMT

Hi wshanewood1 ,

TOTAL UNWEIGHTED:=CALCULATE(SUM([WEIGHTED TOTAL]),ALL(Grades[Category]),ALL(Grades[PAY / PERS])).

Not sure what do you want to calculate here.

Can you share your .pbix file or explain the measures you have calculated and what do they mean.

Regards,

Harsh Nathani

Re: Calculation Issue

wshanewood1 — Wed, 20 May 2020 18:23:24 GMT

I am trying to weigh the weighted categories by the number of transactions. The more transactions, the more weight. So I need the overall transaction number. I use this formula to remove the filters for Category and PAY/PERS that is applied. I get % weight of each transaction, multiple that by each category grade, and then sum that up to give me a weighted grade for those categories. I hop that makes sense.

I removed the filter fo Pay/Pers because it wasnt needed anymore.

TOTAL UNWEIGHTED:=CALCULATE(SUM([WEIGHTED TOTAL]),ALL(Grades[Category]))