calculate percentages

[BullGangGeneral]

how would you do a measure base on this scenario?

I have sales in all 50 states of America, each state has multiple sales, I drag individual sales amount onto a table, as well as the states, now i want to be able to calculate the the percentage each sale is of the total of that state. so lets say new york state has 2 sales, 5 and 10 dollars, and new jersey has 3 sales, 2, 3, 5 dollars each. i want to be able to show the 5 dollar sale in ny is 33% of the total for new york which is 15, and i also want to be able to show the 2 dollar sale for new jersey as 20% for new jersey because new jersey has total 10 dollar in sales.

any help is appreciated.

[Anonymous]

Hi @BullGangGeneral,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak]

@BullGangGeneral , Try a measure

Divide(sum(Table[Sale]), calculate(sum(Table[Sales]), allselected()))

[BullGangGeneral]

meh didn't work for me. i also tried allexcept, it just return percentage of grand total instead of sub total.

[Anonymous]

Hi @BullGangGeneral,

Any update for these? The measure expressions calculation is based on current row content levels.
For this scenario, it means you need to cancel the fields aggregate or add unique fields to prevent the summary on the row level. Then the expression can be calculated on each row instead of showing one row with a summarized value and the result 1 on the total level.

formula =
DIVIDE (
    SUM ( Table[Sales] ),
    CALCULATE (
        SUM ( Table[Sales] ),
        ALLSELECTED ( Table ),
        VALUES ( Table[State] )
    )
)

Regards,
Xiaoxin Sheng