income statement matrix with amount and percentage columns

[Anonymous]

hi,

i need to produce an income matrix as follows:

 

i have the numbers to line up in the amount column (2nd from right). however, i need help to produce the % in the % column (last column on the right). the total income will be the (100%) base. i'd appreciate any help to resolve this.

krgds, -nik 

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Income statement:

You may create a measure as below.

Percentage = 
var _total = 
CALCULATE(
    SUM('Income statement'[Amount]),
    'Income statement'[Category] = "Total Income"
)
return
SELECTEDVALUE('Income statement'[Amount])/_total

Then you may use the matrix visual to display the result.

Best Regards

Allan

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

[Anonymous]

hi @v-alq-msft ,

it looks really good.

since the items i listed in my original matrix graphics earlier are measures, i'm not sure how to use your suggested dax for percentage for them. i'd apprec further suggestion/guidance.

krgds, -nik

[v-alq-msft]

Hi, @Anonymous 

I'd like to suggest you use 'summerize' function to get a table with new columns which calculate with the logic of the measures. Then you may calculate based on the virtual table to get the result.

Best Regards

Allan

[Anonymous]

thank for your suggestion, @v-alq-msft .

i'll try that & revert as necessary.

krgds, -nik

[amitchandak]

@Anonymous , refer if this can help

https://community.powerbi.com/t5/Desktop/Traditional-Financial-Statements/td-p/7223

[Anonymous]

many tks fr the suggested solutions in the weblink in your email reply, @amitchandak.

tho ghuiles' suggested weblink to biccountant.com website seems most relevant to my issue, i probably will delve into that later as it may require me to re-do my data modelling (which may b quite tedious too). 

the issue is not so much in producing a traditional accounting statement using ms power bi. it's more on finding how to create a percentage column in a matrix in the fashion i described earlier. nonetheless, i'll study further those suggestions in the weblink you provided.

many tks again.
krgds, -nik