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

Combine measures in Matrix visual and calculate variance by date and period

Dear datanauts,

 

I am trying to combine measures in a matrix split by date and period (Actual and Budget).

You can access my files here (please take a copy locally):

https://www.dropbox.com/sh/x2rzap374vven36/AACA94W9ZdKosUEbleaoZ-4ja?dl=0

 

 

Excel Report.jpg

is what I am trying to reproduce from Excel to Power BI

 

Excel Report.JPG

 

 

example.xlsx

is my data source excel file

 

example.pbix

is my power bi model

 

example.xlsx contents

Sheet "Data"

One table named Financials which contains Revenues and Expenses split by month and period.

Another table named Operationals which contains number of visitors split by month and period.

 

Sheet "Calendar"

A table with reported Dates

 

Sheet "Business Unit"

A table with Business Units

 

Sheet "Period"

A table with a split of periods I need to report (Actual, Budget and Last Year)

 

Sheet "Report"

The report I am trying to reproduce from Excel to Power BI

 

I have created the following model in Power BI

 

model.JPG

 

 

Measures Approach 1

I have created the following measures

 

Revenues = calculate(sum(Financials[Value]),Financials[Category]="Revenues")

Visitors = sum(Operationals[Value])

Revenues per Visitor = iferror([Revenues]/[Visitors];0)

 

 

 

This approach (Measures approach 1) is not working as I can't display Last Year and their variance from Actuals in Table 1 matrix visualTable 1.JPG

 

Measures Approach 2

I have created the following measures:

Revenues by Period =

switch(

 true(),

 values(Period[Period])="Actual",[Revenues Actual],

 values(Period[Period])="Budget",[Revenues Budget],

 values(Period[Period])="LY",[Revenues LY])

 

Visitors by Period =

switch(

 true(),

 values(Period[Period])="Actual",[Visitors Actual],

 values(Period[Period])="Budget",[Visitors Budget],

 values(Period[Period])="Actual",[Visitors LY])

 

Revenues per Visitor by Period = [Revenues by Period]/[Visitors by Period]

 

 

 

This approach (Measures approach 2) is not working as Last Year is not displayed although included in the switch (Table 2 matrix visual).Table 2.JPG

My hint is that "Actual" is mentioned twice and switch is reading the first line.

Additionally, I think I am not able to create in columns the variances from Actuals.

 

Approach 3

I have created the follwing measures under Financials table:

Revenues Actual = calculate(sum(Financials[Value]),Financials[Category]="Revenues",Financials[Period]="Actual")

Revenues Budget = calculate(sum(Financials[Value]),Financials[Category]="Revenues",Financials[Period]="Budget")

Revenues LY = calculate([Revenues Actual],SAMEPERIODLASTYEAR('Calendar'[Reported Date]))

Revenues Δ Act vs Bgt = [Revenues Actual]-[Revenues Budget]

Revenues % Act vs Bgt = iferror([Revenues Δ Act vs Bgt]/[Revenues Budget];0)

Revenues Δ Act vs LY = [Revenues Actual]-[Revenues LY]

Revenues % Act vs LY = iferror([Revenues Δ Act vs LY]/[Revenues LY];0)

 

 

 

Again this approach is not working (Table 3 matrix visual) as I would like measures to be listed vertically.Table 3.JPG

 

Any help would be much appreciated.

Once again my target is to reproduce in Power BI the report displayed at Excel Report.jpg

 

In case you come up with a solution feel free to upload your files in the same link with different file names.

 

Thank you for your time!!!

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Combine measures in Matrix visual and calculate variance by date and period

Hi @imar ,

 

You can create your measures like so:

Actual 2 =
VAR t =
    UNION ( Financials, Operationals )
RETURN
    IF (
        HASONEVALUE ( Category[Category] ),
        SUMX ( FILTER ( t, [Period] = "Actual" ), [Value] ),
        DIVIDE (
            SUMX ( FILTER ( t, [Period] = "Actual" && [Category] = "Revenues" ), [Value] ),
            SUMX ( FILTER ( t, [Period] = "Actual" && [Category] = "Visitors" ), [Value] )
        )
    )
Budget 2 =
VAR t =
    UNION ( Financials, Operationals )
RETURN
    IF (
        HASONEVALUE ( Category[Category] ),
        SUMX ( FILTER ( t, [Period] = "Budget" ), [Value] ),
        DIVIDE (
            SUMX ( FILTER ( t, [Period] = "Budget" && [Category] = "Revenues" ), [Value] ),
            SUMX ( FILTER ( t, [Period] = "Budget" && [Category] = "Visitors" ), [Value] )
        )
    )
Δ Act vs Bgt 2 = IF ( HASONEVALUE ( Category[Category] ), [Actual 2] - [Budget 2], BLANK () )
% Act vs Bgt 2 = iferror([Δ Act vs Bgt 2]/[Budget 2],0)
LY 2 = CALCULATE ( [Actual 2], SAMEPERIODLASTYEAR ( 'Calendar'[Reported Date] ) )
Δ Act vs LY 2 = IF(HASONEVALUE(Category[Category]), [Actual 2]-[LY 2],BLANK())
% Act vs LY 2 = iferror([Δ Act vs LY 2]/[LY 2],0)

revenues.PNG

This is my PBIX file.

 

Best Regards,

Icey

 

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

Community Support Team
Community Support Team

Re: Combine measures in Matrix visual and calculate variance by date and period

Hi @imar ,

 

Maybe you can try which in this PBIX file 'My Measures 2'. This method is a bit cumbersome. If I have a better way, I will reply you immediately. You can also open another post to let more people help you.

 

Best Regards,

Icey

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Combine measures in Matrix visual and calculate variance by date and period

Hi @imar ,

 

You can create your measures like so:

Actual 2 =
VAR t =
    UNION ( Financials, Operationals )
RETURN
    IF (
        HASONEVALUE ( Category[Category] ),
        SUMX ( FILTER ( t, [Period] = "Actual" ), [Value] ),
        DIVIDE (
            SUMX ( FILTER ( t, [Period] = "Actual" && [Category] = "Revenues" ), [Value] ),
            SUMX ( FILTER ( t, [Period] = "Actual" && [Category] = "Visitors" ), [Value] )
        )
    )
Budget 2 =
VAR t =
    UNION ( Financials, Operationals )
RETURN
    IF (
        HASONEVALUE ( Category[Category] ),
        SUMX ( FILTER ( t, [Period] = "Budget" ), [Value] ),
        DIVIDE (
            SUMX ( FILTER ( t, [Period] = "Budget" && [Category] = "Revenues" ), [Value] ),
            SUMX ( FILTER ( t, [Period] = "Budget" && [Category] = "Visitors" ), [Value] )
        )
    )
Δ Act vs Bgt 2 = IF ( HASONEVALUE ( Category[Category] ), [Actual 2] - [Budget 2], BLANK () )
% Act vs Bgt 2 = iferror([Δ Act vs Bgt 2]/[Budget 2],0)
LY 2 = CALCULATE ( [Actual 2], SAMEPERIODLASTYEAR ( 'Calendar'[Reported Date] ) )
Δ Act vs LY 2 = IF(HASONEVALUE(Category[Category]), [Actual 2]-[LY 2],BLANK())
% Act vs LY 2 = iferror([Δ Act vs LY 2]/[LY 2],0)

revenues.PNG

This is my PBIX file.

 

Best Regards,

Icey

 

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

imar Frequent Visitor
Frequent Visitor

Re: Combine measures in Matrix visual and calculate variance by date and period

Hi @Icey ,

 

Thank you for looking into this.

Your help is much appreciated!

 

In my real case, I want to add 10 more ratios below the financial and operational data. I am wondering how this is going to work with the Row subtotals label approach you suggested.

For example, assume that I want to calculate Expenses per Visitor or other ratios like Average stay per visitor (data not currently provided in my excel file). In that case how are we going to add these ratios below the table you created?

To my understanding Row subtotals level works only for one ratio (Revenues per visitor). How about the rest suggested ratios?

Do you think there is a quicker way to model my data (e.g. Financials and Operationals in one table) to make power bi more efficient?

 

Thank you in advance for you time!

 

Best Regards,

Imar

Community Support Team
Community Support Team

Re: Combine measures in Matrix visual and calculate variance by date and period

Hi @imar ,

 

For serveral ratios like 'Revenues per visitor', this method is not feasible. I am looking for other ways, and I will respond to you as soon as I make progress.

 

Best Regards,

Icey

 

Community Support Team
Community Support Team

Re: Combine measures in Matrix visual and calculate variance by date and period

Hi @imar ,

 

Maybe you can try which in this PBIX file 'My Measures 2'. This method is a bit cumbersome. If I have a better way, I will reply you immediately. You can also open another post to let more people help you.

 

Best Regards,

Icey

View solution in original post

imar Frequent Visitor
Frequent Visitor

Re: Combine measures in Matrix visual and calculate variance by date and period

Hi @Icey ,

 

Once again I would like to thank you for your time!

Your posts have inspired me to work this futher. I came up with the following solution:

1. I created a table with all measures.

2. I used the switch function to select these measures.

My solution.pbix is saved in the same folder:

https://www.dropbox.com/sh/x2rzap374vven36/AACA94W9ZdKosUEbleaoZ-4ja?dl=0

 

Best Regards,

Imar

 

 

 

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)