divison calculation for the same column

[Anonymous]

Each dealer has four types of contracts they can sell.

grouped as OL/CH and HP/FL 

I need to calc the percentage of all their deals which are OL/CH and HP/FL 

How can this be done? Other columns which may help are amount financed which is the total financed amount for each deal              end result should be 

John 30% OL/CH 70% HP/FL

Amy 24% OL/CH 76% HP/FL             

[Anonymous]

I need the forumla to pick up the dealer and then the columns in the table 

for each dealer it needs to have the number of contract types it has by the amount financed and then divide the two types of contracts 

[Anonymous]

This is what it looks like on Excel 

i need this on power bi 

[amitchandak]

@Anonymous , Based on what I see in excel

CH/OL % = divide( sum(Table[CH/OL]), Sum(HP/FL)+sum(Table[CH/OL]))

HP/FL % = divide( sum(Table[CH/OL]), Sum(HP/FL)+sum(Table[CH/OL]))

[Anonymous]

That is closer however, the values CH/OL are in the column called doc type in the data set which is why I need a measure which picks up "ch" and "ol" and calculating all of the amount financed for those 

[Anonymous]

Hi @Anonymous ,

Can you provide the pbix of the sample data? I don't quite understand what the doc type is. The attachment is the pbix file I created by myself, you can refer to it.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi

I've used this calculation however the values are under the wrong headings 

CH/OP result is more likely to be the result for HP/FL

How can i switch the two so the results are more accurate 

[Anonymous]

CH/OL% = DIVIDE(SUM('Key Measures'[CH/OL],SUM('Key Measures'[CH/OL])+SUM('Key Measures'[1 HP/FL]),0))

Why does this calculation not work for me? it wont pick up the table that the columns are in

also this message comes up

Too many arguments were passed to the SUM function. The maximum argument count for the function is 1.

[Anonymous]

CH/OL = CALCULATE(SUM('Detail'[Amount Financed]),'detail'[doc type]<>"Operating Lease",'detail'[Doc type]<>"Contract Hire")

Im using this but its not actually picking up the data correctly 

What is wrong with the calculation

[Anonymous]

Hi @Anonymous ,

Try 

CH/OL = CALCULATE(SUM('Detail'[Amount Financed]),FILTER('Detail','Detail'[doc type]<>"Operating Lease" && 'Detail'[Doc type]<>"Contract Hire"))

If you want to remove other filters, add the ALL/ALLSELECTED function

CH/OL = CALCULATE(SUM('Detail'[Amount Financed]),FILTER(ALL('Detail'),'Detail'[doc type]<>"Operating Lease" && 'Detail'[Doc type]<>"Contract Hire"))

or

CH/OL = CALCULATE(SUM('Detail'[Amount Financed]),FILTER(ALLSELECTED('Detail'),'Detail'[doc type]<>"Operating Lease" && 'Detail'[Doc type]<>"Contract Hire"))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Step 1: calculations are diff tried two but give same results but this is the issue as data is not being picked up correctly 

CH/OL = CALCULATE(SUM('Detail'[Amount Financed]),FILTER('Detail','Detail'[doc type]<>"Operating Lease" && 'Detail'[Doc type]<>"Contract Hire"))

1 HP/FL = CALCULATE(SUM('Detail'[Amount Financed]),'detail'[doc type]<>"Hire Purchase",'detail'[Doc type]<>"Finance Lease")

step 2:

CH/OL plus 1 HP/FL =

[CH/OL] + [1 HP/FL]

Step 3:

CH/OL% = Divide ('Key Measures'[CH/OL],'Key Measures'[CH/OL plus 1 HP/FL],0)

HP/FL% = Divide ('Key Measures'[1 HP/FL], 'Key Measures'[CH/OL plus 1 HP/FL],0)

[Anonymous]

Just a thought but could it be <> in the formula as I need it to add up amount financed values for all of the CH and OL 

[Anonymous]

almost solved the issue

So I am using the fact table to get the figures but the details for the managers are coming from a connecting table as i need to use two table for a differernt calculation 

therefore, if i add the manager details from the same table it works but if i use the connecting table it does not work.

but that means the other calculation i need will not work so either way there is an issue 

What can i di to make both work or use the connecting table 

[Anonymous]

Also changed formula to this =

HP/FL 2 = CALCULATE(SUM(Detail[Amount Financed]), FILTER(Detail,Detail[Doc Type] IN {"Hire Purchase", "Finance Lease"}))

CH/OL 3 = CALCULATE(SUM(Detail[Amount Financed]), FILTER(Detail,Detail[Doc Type] IN {"Contract Hire", "Operating Lease"}))

[Anonymous]

This is still not picking up the data correctly 

Is there any other explanation for why it may not work?

The calculation method for the rest of the process is correct but its the first calculation which is the issue which means the results for the rest of the process are an issue 

[Anonymous]

Hi @Anonymous ,

Sorry, not very clear, can you provide me with pbix file? Remember to hide private data.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

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

divide( countrows(Table), calculate(countrows(Table), allexcept(Table, Table[Name])))

Percent of SubTotal or Total: https://www.youtube.com/watch?v=6jTildcV2ho&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=37