How can I create this table?

Question

Hello&nbsp;I have a data table with various fields, e.g. customer, price, product code, country, etc.&nbsp;I want to calculate some measures/metrics on that data, e.g. percentage of customers that are in Europe, percentage of product costs starting with A*, number of unique product codes, average product price etc.&nbsp;I want to put these metrics in an output visual (table? matrix? something like that) like this:Metric 1Percentage of customers that are in Europe74%Metric 2Percentage of product costs starting with A*28%Metric 3Number of unique product codes529Metric 4Average product price$42.20&nbsp;This should allow me to filter the rows using a slicer.&nbsp;How can I do this? Please let me know the steps as I am not familiar with it (including if I need to create an additional data table or something).&nbsp;Thanks!

amitchandak · Answer

Anonymous , In matrix You can use Show on row to move measure on row 
https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/
&nbsp;
Measure Slicer : https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
&nbsp;
Dynamically change chart axis in Power BI https://www.youtube.com/watch?v=6jeSIRpjv0M

anonymous · Answer

Thanks.I know the formulas for each of the measures I want to calculate. I also know how to name each of these measures. But how do I put these into a MeasureName; MeasureResult table visual?&nbsp;It is not clear from your answer. Do I have to create a new data table or something, first and then use a Matrix visual?&nbsp;

v-alq-msft · Answer

Hi,&nbsp;Anonymous&nbsp;
&nbsp;
Based on your description, I created data to reproduce your scenario.
Table:

&nbsp;
You may create measures as below.
&nbsp;
Percentage of customers that are in Europe = 
DIVIDE(
    CALCULATE(
        DISTINCTCOUNT('Table'[Customer]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Region] = "Europe"
        )
    ),
    CALCULATE(
        DISTINCTCOUNT('Table'[Customer]),
        ALLSELECTED('Table')
    )
)
Percentage of product costs starting with A* = 
DIVIDE(
    CALCULATE(
        DISTINCTCOUNT('Table'[Product code]),
        FILTER(
            ALLSELECTED('Table'),
            LEFT('Table'[Product code],1)="A"
        )
    ),
    CALCULATE(
        DISTINCTCOUNT('Table'[Product code]),
        ALLSELECTED('Table')
    )
)
Number of unique product codes = 
CALCULATE(
    DISTINCTCOUNT('Table'[Product code]),
    ALLSELECTED('Table')
)
Average product price = 
CALCULATE(
    AVERAGE('Table'[Price]),
    ALLSELECTED('Table')
)
&nbsp;
&nbsp;
Then you may use a matrix visual to display the result, make the matrix selected, go to 'Visualizations', click 'Values', set 'Show on rows' as on. Here is the result.

&nbsp;
Best Regards
Allan
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

v-alq-msft · Answer

Hi,&nbsp;Anonymous&nbsp;
&nbsp;
If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.
&nbsp;
Best Regards
Allan

Forum Discussion

How can I create this table?

4 Replies

Related content

Create new table

Lakehouse Table Generate Create Table

Create rows from 2 tables

Creating Date Tables

Creating a couple of tables

Metric 1	Percentage of customers that are in Europe	74%
Metric 2	Percentage of product costs starting with A*	28%
Metric 3	Number of unique product codes	529
Metric 4	Average product price	$42.20