Assistance with a suitableType Of Visualisation

[nkaruwo]

A. I have been using and implementing PowerBI for the last couple of months and I am sort of familiar with the product.  I have a requirement from one of my clients to produce a report  that shows the Branches on the colums and Some calculated measures on the rows.  I have tried the matrix ,table visualisations but its not bringing what I am looking.  Anyone please assist for me to produce a visualisation like the one attached.

B.

Branch	Branch A	Branch B	Branch C
YTD Sales	2000	5000	8000
YTD Target	800	1500	3500
SHortfall	1200	3500	4500
Measure 1	300	500	6000
Measure 2	200	150	8777

Thank you in advance

[v-alq-msft · Accepted solution]

Hi, @nkaruwo 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Test:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is a relationship between 'Calendar' and 'Table'. You may create measures as below.

Result = 
var tab = 
ADDCOLUMNS(
    CROSSJOIN(
        DISTINCT('Table'[Branch]),
        DISTINCT(Test[Branch Measure])
    ),
    "Result",
    SWITCH(
        [Branch Measure],
        "YTD Sales",
        CALCULATE(
            SUM('Table'[Sales]),
            DATESYTD('Calendar'[Date])
        ),
        "YTD Target",
        CALCULATE(
            SUM('Table'[Target]),
            DATESYTD('Calendar'[Date])
        ),
        "Min Sales",
        CALCULATE(
            MIN('Table'[Sales]),
            FILTER(
                ALL('Table'),
                'Table'[Branch]=EARLIER('Table'[Branch])
            )
        ),
        "Max Sales",
        CALCULATE(
            MAX('Table'[Sales]),
            FILTER(
                ALL('Table'),
                'Table'[Branch]=EARLIER('Table'[Branch])
            )
        )
    )
)
return
SUMX(
    tab,
    [Result]
)

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.

[simrantuli]

Hi @nkaruwo,

I had a similar requirement from a client. This is how I achieved it.

Let's say your data looks like below.

Create a new table (Enter Data) and mention all the measure names that you want to display as rows.

Create a new table by doing cross join of the above two tables.

Now, create a matrix with Rows as 'KPI' from the new table and Columns as 'Branch' from the new table and create a measure using SWITCH like below.

And use this measure in your matrix.

Output:

Best Regards

Simran Tuli

[pranit828]

Hi @nkaruwo 

I would pivot the table which has the branch column and select what calculation I need.

https://www.youtube.com/watch?v=OYM26ZytimM

[nkaruwo]

Thank you for the reposse. Am however looking for an additional way of showing multiple measures across diffrent branches on the rows  The option seems to work with only one measure.