Is a way to achieve a view of a table/matrix with measures and graphs?

[UsePowerBI]

Hello

I want to create the following visual:

Title1, Calculation1, Gauge1, Trendline1

So, it is basically a table or matrix of however you want to call this 2D array that each row has the Title of a calculation, then the result of the Calculation (e.g. COUNTROWS(TableX)) then the Gauge graph of that Calculation1 and last the Trendline1 of that calculation across different dates.

Can you tell me please how to achieve this?

How to structure the data and how to visualise it?

Otherwise, is there any free plugin that allows that?

The reason I want these calculations/graphs in a list is because they are too many, 100+ and they need to be dynamically filtered (by their name, their code, their owner, etc).

Thanks!

[parry2k]

@UsePowerBI it is not clear to me what you are trying to achieve? Can you please share sample data and expected output.

[UsePowerBI]

Hi

The data is confidential and it really does not matter what it is exactly. You may consider any kind of data, e.g. customer, product, price, country, etc, it does not really matter.

I want to calculate some measures. E.g. percentage of Europe customers, percentage of X products, lower price, etc. Again, it does not really matter.

What output I want, is one table. Each row of the table will have 4 cells. Each cell will be:

Description of the measure; Result of the measure; Gauge gaph of the measure; Trendline of the measure (using historical data of datasets of different dates).

PowerBI allows you to create the above as individual visuals. But I want them to be in one line of a table and I will add more lines.

I want to put all these four visuals into one line so that I can filter the table to display only specific lines.

I hope that is clear?

[parry2k]

@UsePowerBI why you cannot put a simple data in an excel sheet with the expected output. Sample data can be anything (ofcourse it doesn't need to be real data) if you cannot do that part, not sure how you expect someone to help. Thanks!

[UsePowerBI]

OK I will rephrase, hope it make sense:

In this link https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-tables you see the table visual 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 visual where I will have one row with the first cell being '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!

[UsePowerBI]

Hope it is obvious that the table:

Womens Product 1 Price1 Last Year Sales1

Womens Product 2 Price2 Last Year Sales2

etc

Is like:

Womens Product      Price      Last Year Sales

ABC1                           $34             $65457

ABC2                           $95             $95685

etc

[parry2k]

@UsePowerBI you have to add a measure for each column you want to see

Avg = AVERAGE( Table[Price] )

Sum Last Year Sales = SUM ( Table[Last Year Sales] )

in table or matrix visual, drop the product on rows and above measures in the values, and see if that is what you are looking for.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[UsePowerBI]

Nop, it is not what I am asking.

Let me go over again in a simpler way:

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%

Is it clearer now? I want a list of the measures together with a description for each.

I suspect I need to create another table to do that but not sure.

Any idea?

[v-gizhi-msft · Accepted solution]

Hi,

Please take following steps:

1)Create a new slicer table:

Table 2 = 
DATATABLE (
    "Slicer", STRING,
    {
        { "Percentage of US Sales" },
        { "Percentage of DE Sales" }
    }
)

2)Try this measure:

Measure = 
VAR a =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Country] = "US" )
    )
VAR b =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Country] = "DE" )
    )
VAR c =
    CALCULATE ( SUM ( 'Table'[Sales] ), ALL ( 'Table' ) )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Table 2'[Slicer] ),
        "Percentage of US Sales", a / c,
        "Percentage of DE Sales", b / c
    )

3)The result shows:

See my attached pbix file.

Best Regards,

Giotto

[UsePowerBI]

It works but not what I really need. It's hard to explain it.

In the example table of the screenshot, the first column is Category and it contains '010-Womens', '020-Mens' etc.

This column appears in the source data:

Product Category;  Product Code;  Price;  Sales;

However, I want the first column to be something totally irrelevant, not a column that exists in the source data. E.g. I want the table to be like:

Name_of_a_Measure; Measure

The first column, i.e. Name_of_a_Measure, would be some text that does not exist in any source table.

The second column, i.e. Measure, would be a particular measure that applies to a particular source table.

How can I achieve this? I guess I need to create a new table or something but not sure what exactly.

Do I need to create a table like this?

Name_of_a_Measure; Product Category;  Product Code;  Price;  Sales;

However all the "Product Category;  Product Code;  Price;  Sales;" lines will not apply to a particular Name_of_a_Measure since the measures will be calculated based on all the rows of the table.

Hope this makes sense? Is it doable?

In other words, how do I link a list of measure names with their respective measures for all to be displayed in one table?

E.g.:

MeasureName1; Measure1

MeasureName2; Measure2

Thanks!