How to make Totals in powerBI matrix to be not SUMmed for each column?
I have a percentage measure which says which project made which ratio of revenue out of whole portfolio for each month, but the % are SUMmed for Total.
Example:
Is there a way how to put average of % instead of SUM in Total?
Measure I am using:
Klient/Portfolio Ratio = DIVIDE('POHODA EXPORT'[SUMX(price)]; 'POHODA EXPORT'[MeasurePortfolio])
When MeasurePortfolio is:
MeasurePortfolio = CALCULATE(SUMX(DISTINCT('POHODA EXPORT'[Concatenate]);FIRSTNONBLANK('POHODA EXPORT'[Suma Portfolio] ;0))/DISTINCTCOUNT('POHODA EXPORT'[Concatenate]))
and Suma Portfolio is a column calculated as:
Suma Portfolio =
var PM1='POHODA EXPORT'[PMs.PM]
var month1 =MONTH('POHODA EXPORT'[1.MM.YYYY])
var year1 = YEAR('POHODA EXPORT'[1.MM.YYYY])
return CALCULATE(SUM('POHODA EXPORT'[Suma bez DPH]);FILTER('POHODA EXPORT';'POHODA EXPORT'[PMs.PM] = PM1 && MONTH('POHODA EXPORT'[1.MM.YYYY])= month1 && YEAR('POHODA EXPORT'[1.MM.YYYY])=year1))
Concatenate is a column which makes a unique reference from Month, Year and Project Manager to able to proceed with requirements.
What I am trying to achieve is out of data certain PM has assigned portfolio of projects, and get them a ratio percentage for project/portfolio.
Mentioned formulas works succesfully, but then Subtotal has a wrong value, when Subtotal SUMS percentage for each month instead of averaging them, so for one project it make the percentage 400% Subtotal.
I don`t understand the logic how Subtotal is calculated, I calculate project Margin in percentage, but % for each month is not SUMmed in Total. And also there are calculations using measures and columns. I tried to google but not succesfully.
Thanks
