Percent of Sales

[MapleFox36]

Hello, I am trying to create a measure to be able to display account as a % of Sales.

My dataset is an account export that is already mapped, and so Sales and Expenses aree all in the same table.

So far I have 3 measures.

Total Value = sum([Amount DrCr])

Total Revenue = =CALCULATE([Total Value],ALLSELECTED(),tbl_COA_Reference[Level4]="Revenue") , where Level 4 is part of the account mapping.

% Revenue = DIVIDE([Total Value],[Total Revenue],0)

It works fine when I am only showing 1 period, but when I extend with multiple periods in the pivot table columns, the %s displayed change to % of row instead of % of sales (see attached for correct single-period, and incorrect multi-period).

I assume the error is on the Total Revenue measure, but any help is appreciated

[v-cazheng-msft]

Hi @MapleFox36 ,

May I know whether your issue has been resolved? If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problem, please provide some sample data contained in your tables and let us know their relationships so we can help you find the solution. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[Whitewater100 · Accepted solution]

Hi:

You can obtain the share percent a few different ways. One thing though, your measure for Total Value would usually look like Total Value = SUM(TableName, [Column Name]) and that is not what is shown above. Seeing your data (Fact Table layout and Dimension for Accounts)could help answer correctly. That mentioned, A couple of ways you can try is:

Share of Value 1 =

DIVIDE([Total Value],

CALCULATE([Total Value], REMOVEFILTERS()))

or Share of Value 2 = 

 DIVIDE(SUMX(YOURTABLENAME, [Total Value]),

SUMX(ALLSELECTED(YOURTABLENAME), [Total Value]))