Add Row with Percentage to Table visual

[RolandBAV]

Hi everybody

This is my first post in this forum and I am new to Power BI.

Actually I am creating a dashboard on which I would like to show the total sum of sales in one row and the percentage in another row. So the result should look as follows:

The source data would look like that:

A secound table should show the customer count per MarketArea and look like that:

Currently I am fetching the data from the database. In the database I created a view that aggregates the data and using a union I get the sums and the percentages in two rows.

The problem is, that the drill-trough is not possible as my view does not cotain the single customer records.

How can the result be achieved in Power BI?

Thanks in advance for your help.

Regards,

Roland

[amitchandak · Accepted solution]

@RolandBAV , Create measures

sales = Sum(Table[Sales Value])

Clients = distinctcount([client ID])

% of Sales = divide([sales], calculate([sales], removefilters(Table[Market Area])) )

% of client = divide([Clients], calculate([Clients], removefilters(Table[Market Area])) )

You can now use these measures on Matrix visual- With option - Switch values to row

Switch values to row - https://youtu.be/cN8AO3_vmlY?t=11650

Percent of Total and Percent of SubTotal https://www.youtube.com/watch?v=6jTildcV2ho
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=24270s

[Uzi2019]

Hi @RolandBAV 

Can you please show case your power bi dataset like how your data is look alike??

just simple take table visual add all your columns into it.  and share screenshot of that if possible.

bcz by looking at the source data your expected output seems quite simple but dont know about actual data ingested in power bi.

[RolandBAV]

Hi @Uzi2019

Sorry, my description was not clear. This is the data I would like to process, meaning the dataset contains this data:

Regards,

Roland

[Uzi2019 · Accepted solution]

Hi @RolandBAV 

If you just wanted to do pivot ofyour data just take matrix visual and enable this option under value section

take your market in column section and sales and percentage in value section

I hope i answered your question!

[amitchandak · Accepted solution]

@RolandBAV , Create measures

sales = Sum(Table[Sales Value])

Clients = distinctcount([client ID])

% of Sales = divide([sales], calculate([sales], removefilters(Table[Market Area])) )

% of client = divide([Clients], calculate([Clients], removefilters(Table[Market Area])) )

You can now use these measures on Matrix visual- With option - Switch values to row

Switch values to row - https://youtu.be/cN8AO3_vmlY?t=11650

Percent of Total and Percent of SubTotal https://www.youtube.com/watch?v=6jTildcV2ho
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=24270s