Forum Discussion
Percentage Calculation
Hi - I tried to search but could not find the exact answer for this simple one - this is a matrix report filtered just to show 2023 deals by lead source. I'd like to add a coulmn that shows the %...
Hi Anonymous ,
Create a new measure.
count of id = var _a=SELECTEDVALUE('Table'[LeadSource(groups)]) var tmp=FILTER(ALL('Table'),[LeadSource(groups)]=_a) var _ctn=COUNTROWS(tmp) var _b=COUNTROWS(ALL('Table')) return IF(HASONEVALUE('Table'[LeadSource(groups)]),_ctn,_b)Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous - thanks for the reply. So the formula runs, but not sure its giving the correct result?
I pointed the DAX to the Opportunity table where the records sit:
Lead Source % of Total =
var _a=SELECTEDVALUE('Opportunity'[LeadSource (groups)])
var tmp=FILTER(ALL('Opportunity'),[LeadSource (groups)]=_a)
var _ctn=COUNTROWS(tmp)
var _b=COUNTROWS(ALL('Opportunity'))
return
IF(HASONEVALUE('Opportunity'[2023 Type]),BLANK(),
IF(HASONEVALUE('Opportunity'[LeadSource (groups)]),DIVIDE(_ctn,_b),1))
But the %'s don't quite look right? Also I'd like to remove the 2 total columns marked with an X and just leave the one marked with a tick.
I've also included a view of the matrix with the Columns removed to show the % (if useful?) plus have filtered our one row that had (Blank) as a row heading (and no values)
Thanks!
Hi Anonymous ,
Create a new measure.
count of id = var _a=SELECTEDVALUE('Table'[LeadSource(groups)]) var tmp=FILTER(ALL('Table'),[LeadSource(groups)]=_a) var _ctn=COUNTROWS(tmp) var _b=COUNTROWS(ALL('Table')) return IF(HASONEVALUE('Table'[LeadSource(groups)]),_ctn,_b)Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.