Cash inflow divided on revenue: doesn't sums in drilldown

[giorgilomidze]

So i have revenue and cash inflow tables for utility company. One table includes information about customers who use services and what amount of money they were charged with - Revenue. second table includs information about what amount of monay did same customers payd to company. i have this information for two months. this customers are classified as several categories. i whant to get information (pivoted) what percentage of payment each category has ( cash inflow/revenue). i have hierarchy as well so when i drill down i get information for different categories.  Hierarchy, category, and month by month everything works perfectly seperately for revenue and for cash inflow as well.  but when i create new column with formula column = Column = IFERROR(Table3[Cash Inflow]/Table3[Net Revenue Vat included],BLANK()) it gives me an unwanted numbers. for example if i have 2 customers under x industri, customer a has revenue of 40USD and cash inflow 30US percentage is 30/40=75% and if b company has revenue of 30US and cash inflow of 45US percentage is 45/30=150%. but after pivoting for industry it will show me 1.5+0.75=2.25 (150%+75%=225%). this is because it sums the results of customers percentages individualy. so here is the question. what should be dax formula of new colum in order to show percentage of X industry: revenue of a company +revenue of company b 40+30 and divide on cashflow for a +cashflow for b thus: (30+45)/(40+30)=1.07%. in the picture is shown industries

[v-shex-msft · Accepted solution]

HI @giorgilomidze,

>>what should be dax formula of new colum in order to show percentage of X industry: revenue of a company +revenue of company b 40+30 and divide on cashflow for a +cashflow for b thus: (30+45)/(40+30)=1.07%.

Current dax formula not smart enough to auto summary the calculated result on total level, it will calculate with summary records to instead.

In my opinion, you need to add conditions and formulas to handling calculation on different levels.

I'd like to suggest you take a look at below link which mentioned about similar requirement.

Clever Hierarchy Handling in DAX

Regards,

Xiaoxin Sheng

[giorgilomidze]

I decided that this quetion was rather vague so i posted another question which was answered and problem was resolved.

https://community.powerbi.com/t5/Desktop/pivot-after-Summing-by-categories/m-p/345042#M154772

with divied function

New Measure = DIVIDE(SUM('Table1'[Paid]) , SUM('Table1'[Charged]))

thank you for your answer as well. ito helped me in another related problem