cancel
Showing results 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

Anonymous
Not applicable

## division in a measure

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

Percentage = DIVIDE(SUM(LDet3[Total Chassis]),CALCULATE(SUM(LDet3[Total Chassis]),ALLSELECTED('LDet3'[Age Type])))

Because I can't view the data in Query Editor, I don't add a new column to sort the Age Type values. If you need, you could reference my last answer and modify the formula accordingly.

Best Regards,

Xue Ding

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

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 REPLIES 10
Super User

Edit. I read your question again and realized that you were referring to the row total. Try this instead:

DIVIDE (
[Total Chassis],
CALCULATE ( [Total Chassis], ALL ( Table[BR] )
)

------
Try creating a measure like this:

Percent of Grand Total =
DIVIDE (
[Total Chassis],
CALCULATE ( [Total Chassis], ALL ( Table[BR], Table[Age Type] ) )
)

Or this one if BR and Age Type are from different tables.

Percent of Grand Total =
DIVIDE (
[Total Chassis],
CALCULATE ( [Total Chassis], ALL ( Table1[BR] ), ( Table2[Age Type] ) )
)

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Anonymous
Not applicable

Super User

Column header in a matrix is sorted based on how the sort is set in the data model.  You will have to create a custom  sort in the model itself.  Here is the documentation: https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

You may create a Sort-by column in Power query by creating a conditional column. Something like Sort By = if [Column] = "Aged Car" then 1 else if [Column] = "In Warr" then 2...else...end.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Anonymous
Not applicable

Super User

You need to aggreate your Total Chassis column. So I create a separate measure first to aggreagat it. Example is

Sum of Total Chassis =
SUM ( LDet_3[Total Chassis] )

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Anonymous
Not applicable

Hi,

Community Support

Hi @Anonymous ,

I create a new column in Query Editor to implement that the column name is sorted by what you want. And then In report view, I create a measure to calculate the percentage value. Please try and check if the result is what you want.

• Add new column in Query Editor

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZIxCwIxDIX/ytHZoWmbu94oguCig4OD3CBaxMVBTvz7aj1877KVL+H1I8nx6IK4hVtey6VZnR6fZ0xuWHxwMLj74TTHMnXrHGul0USrVJwMllBx9ubHX/K3eV3u59Lsb+NYvpXkoWhKqtA0JWmhakohwdeWMuRMqYPg5t4cTo/6Sx8gBxykhxh1Z4UUsHr4ULNkzA+47WBIzdNQ57000215/XfYQhk4RA9l4J42ThleoExYFMrA0yFUZc6AMlEo754jZkFnyjxGSBPPAdaEefGzFLpU4knhzeHw5hA3DG8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [BR = _t, #"Age Type" = _t, Chassis = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"BR", Int64.Type}, {"Age Type", type text}, {"Chassis", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Age Type] = "New Car" then 1 else if [Age Type] = "In Warr"then 2 else if [Age Type] = "Out Warr"then 3 else if[Age Type] = "Aged Car"then 4 else 5),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}})
in
#"Changed Type1"
• Sort by the column

• Create a measure
Percentage = DIVIDE([Total Chassis],CALCULATE(SUM('Table'[Chassis]),ALLEXCEPT('Table','Table'[BR])))

Best Regards,

Xue Ding

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

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Community Support

Hi @Anonymous ,

Percentage = DIVIDE(SUM(LDet3[Total Chassis]),CALCULATE(SUM(LDet3[Total Chassis]),ALLSELECTED('LDet3'[Age Type])))

Because I can't view the data in Query Editor, I don't add a new column to sort the Age Type values. If you need, you could reference my last answer and modify the formula accordingly.

Best Regards,

Xue Ding

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

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi omg it worked! Thank you so much! ❤️
btw for the sorting of the age type do you have any other solution since i cannot find the data in query editor? 😞

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.