Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[B B]

Hello

In this link https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-tables you see the table below:

How can I create that table when my data is like the Table1 below:

Womens Product 1      Price1     Last Year Sales1

Womens Product 2      Price2     Last Year Sales2 

etc?

Basically, I need to create a table where I will have the row 010-Womens Products and next to it, I will have AVG(Table1[Price] and SUM(Table1[Last Year Sales]) as values in the cells to the right.

Any idea?

Thanks!

——————————
B B
——————————

[Vincent Lacomme]

Hi BB,

Can you post a screencapture of your table model ?

The Last Year sales can be computed with the SAMEPERIODLASTYEAR function in a measure.

——————————
Vincent L.
Chartered accountant – Expert-comptable
——————————
——————————————-

[B B]

Hi Vincent, thanks. This is not a question on how to perform some calculations. This is a question on how to create a table visual.

Let me explain with a simpler example;

I have the source table:

Country; Sales;

US; 5000

UK; 3000

DE; 4000

I want to create the table visual:

Percentage of EU Sales; 58%

Percentage of DE Sales; 33%

I know how to do the calculations for the 58% and the 33%. What I do not know is how to create the above visual, i.e. a table with the description of what I am calculating and next to it the actual calculation/measure.

Any idea?

——————————
B B
——————————
——————————————-

[Vincent Lacomme]

B B,

I’m not sure I completely understand what you need.
But enclosed is an example.

1) Simple matrix with percentage of total (on the value field in the visualization panel click on “Show value as” / “Percent of grand total”)
2) Card with a measure so as to group the Europe sales
3) Table of sales per country

Hope that helps

——————————
Vincent L.
Chartered accountant – Expert-comptable
——————————
——————————————-

[B B]

Thanks for this. However, I do not want the first column to show UK, DE, US, etc.

I do not want the table to show:
DE; 25%
UK; 33%
US; 41%

I want to display the following:

Percentage of DE Sales; 25%
Percentage of UK Sales; 33%
Percentage of US Sales; 41%

I may have to write down these pieces of text (contained in the first column of the above table) in another table or something and somehow merge/combine them or look up them. I may also need to use Calculated Columns instead of Measures or something. Again that is fine, but I am not sure how to do it.

Any idea?

——————————
B B
——————————
——————————————-

[Vincent Lacomme]

This might not be the most efficient way but I created a measure to contenate your text with the field. However, you need to keep the country code in the matrix.
Enclosed is the PBIX.

——————————
Vincent L.
Chartered accountant – Expert-comptable
——————————
——————————————-

[B B]

Thanks for this, however I must not show the Country Codes.

In fact, in addition to the description column, there will be more than one column with descriptions, codes and other data that need to be linked to specific measures.

The end table/matrix would look like:

MetricName1, MetricCode1, MetricGroup1, ValueOfActualMetric1
(by Metric you can consider Measure, e.g. percentage of blank cells in a specific column of a table, max or min of specific column in a table etc)

So I guess I need to create a new table to do that. I have a table with the first three columns mentioned above. However, I need to somehow insert/join the last column, which is specific metrics/measures.

I was thinking to insert an Index column in my table with the first three description/code/etc columns.
I then wanted to create another table where the headers will match the numbers in the Index column mentioned above. So, by adding Calculated Columns, I would have a table like:
1                          2
Measure1            Measure2               etc

But I do not know how to join those two tables. The strange thing is that the Calculated Columns do not appear under Edit Queries, so I have no way of transposing that table and then join it with the table of the description/code/etc columns.

Any idea or alternative solution?

——————————
B B
——————————
——————————————-