Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
fischelr01
Frequent Visitor

Divide multiple columns by one column

I have a measure that counts the number of funnel deals per stage. 

 
Deal Count = CALCULATE(COUNT('FY, CW, CL Combined'[Stage Count]))
 

I need to create a calculation that divides Stage-1 through Stage - 5 by stage 6.... in other words

 

Stage-1/Stage-6

Stage-2/Stage-6

Stage-4/Stage-6

Stage-5/Stage-6

Stage-6/Stage-6

 

The count measure was very straight forward.  Can't figure out how to make a measure that only includes stages 1-5, and a measure that only includes stage 6, allowing me to divide the former by the ladder.

 

fischelr01_0-1572366133613.png

 

Above is my count table, by stage.  For example of what I'm looking for, I'd need:

 

525/7670

393/7670

376/7184

278/7184

.

.

.

Thanks in advance!

5 REPLIES 5
Nathaniel_C
Super User
Super User

Hi @fischelr01 ,

You can do this in Power Query.  Here is my solution.Solution 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





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

Proud to be a Super User!




@Nathaniel_C 

 

I'm having trouble extracting your file.  Is it possible to describe your solution in this thread?

 

Thanks!

Hi @fischelr01 ,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Prospect = _t, Evaluate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Prospect", Int64.Type}, {"Evaluate", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Prospect]+[Evaluate]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Total"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom", each Value.Divide([Prospect],[Total])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Percentage.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Pros %"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns1", "Evaluate %", each Value.Divide([Evaluate],[Total])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"Evaluate %", Percentage.Type}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type2", "Total %", each Value.Divide([Total],[Total])),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom3",{{"Total %", Percentage.Type}, {"Pros %", type number}}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Changed Type3",{{"Pros %", Percentage.Type}})
in
    #"Changed Type4"

 Here is the advanced editor in Power Query. If you go to the Home tab in Power Query, you will see it.  Click on it to open, and then paste the above into it.  If you do this in a new Power BI, you may have to enter a little table to see it active. Here is my table. Create this, then open the Advance Editor, copy and paste right over the code that is there.  You will see the steps on the right side, and you can walk through each step to see what I did.
per1.PNG

 


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 





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

Proud to be a Super User!




Nathaniel_C
Super User
Super User

Hi @fischelr01 ,

Not sure if I understand...would you provide what your expected outcome would look like?

 

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Nathaniel





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

Proud to be a Super User!




So, trying to mimic this from Excel.

 

Stages 1-5 are all divided by stage-6.

 

fischelr01_0-1572374919287.png

 

fischelr01_1-1572374950444.png

 

Thanks!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors