cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Assistance with a suitableType Of Visualisation

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.

BranchBranch ABranch BBranch C
YTD Sales200050008000
YTD Target80015003500
SHortfall120035004500
Measure 13005006000
Measure 22001508777

 

Thank you in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Hi, @nkaruwo 

 

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

Table:

c1.png

 

Test:

c2.png

 

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:

c3.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

4 REPLIES 4
Highlighted
Community Champion
Community Champion

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

 


Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.


Appreciate your Kudos, Press the thumbs up button!!👍


Regards,
Pranit


Highlighted

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.

 

 

Highlighted
Responsive Resident
Responsive Resident

Hi @nkaruwo,

 

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

Let's say your data looks like below.

simrantuli_0-1597587903649.png

 

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

simrantuli_1-1597587959111.png

 

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

simrantuli_2-1597588002337.png

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.

simrantuli_3-1597588101338.png

And use this measure in your matrix.

Output:

simrantuli_4-1597588136698.png

Best Regards

Simran Tuli

 

Highlighted
Community Support
Community Support

Hi, @nkaruwo 

 

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

Table:

c1.png

 

Test:

c2.png

 

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:

c3.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors