Percent of column formula

[mrpowrbihelp]

Hi everyone,

I'm trying to make a percent of column formuIa without using "show value as" so i can adjust the decimal places.

I can use the below formula and get it to work for me when I have collection name as the rows in my visual.

ST Dollars = Calculate(Sum('ST Sales'[Dollars]))

% Sales by Collection = DIVIDE([ST Dollars],CALCULATE([ST Dollars],ALLSELECTED('Description'[Collection Name])))

But I have another outside table called "Rank Area" that links to the "Description Table" using the Area column in each table, I use this table to sort the data to show in the custom order I want. Using Area from the "Rank Area" table in the rows of my visual, but this formula doesn't work.

Rank - Area

1   -   Area 1

2   -   Area 2

3   -   Area 3

% Sales by Rank Area = DIVIDE([ST Dollars],CALCULATE([ST Dollars],ALLSELECTED('Rank Area'[Area])))

It works if i use the "show value as" but i can't figure out the formula behind it.

Any suggestions? Thank you in advance.

[v-lionel-msft]

Hi @mrpowrbihelp ,

Please try this formula.

Plese provide sample .pbix file if the formula can't solve your problem.

% Sales by Collection = 
VAR ST Dollars = SUM( 'ST Sales'[Dollars] )
RETURN
DIVIDE(ST Dollars,CALCULATE( SUM('ST Sales'[Dollars]),ALLSELECTED('ST Sales') ))

Best regards,
Lionel Chen

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

---

[parry2k]

@mrpowrbihelp change to this:

% Sales by Rank Area = 
DIVIDE(
[ST Dollars],
CALCULATE([ST Dollars],ALLSELECTED())
)

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡