Upload an excel table in order to keep specific grouping

[lw24]

Hello All!

I've included an excel file but I'm looking for a solution that would allow me to recreate the following table as shown because my stakholders can be very picky...

I'm open to any suggestions! 

Sector	Name	Goal	Prorated Goal	Revenue	Revenue against Prorated Goal
Asset Wealth Management	Joe Smith	$      100,000	$                50,000	$        40,000	80%
Asset Wealth Management	Michael Young	$      200,000	$              100,000	$        10,000	10%
AWM Total		$      300,000	$              150,000	$        50,000	33%
Banking	Bruce Wayne	$      100,000	$                50,000	$        40,000	80%
Banking	Mr. Freeze	$      200,000	$              100,000	$        10,000	10%
Banking Total		$      300,000	$              150,000	$        50,000	33%
Grand Total		$      600,000	$              300,000	$      100,000	33%

[gmsamborn]

Hi @lw24 

Using Power Query, I would filter out all Total records.  Otherwise, you'll have those records added to any totals.

You won't need them anyways since Power BI is quite capable of calculating any required totals.

(Also, [Revenue against Prorated Goal] can easily be calculated.)

I hope this helps.

[lw24]

Thanks for quick reply! To clarify, what I'm looking for is for that information to look like that but in a table visual... Each sector has a total with a grand total of the sector totals at the bottom. 

[gmsamborn]

Hi @lw24 

Here is an example of a matrix that should cover your requirement.

I didn't use an Excel file, I just copied from your above data.  The procedure to follow is the same.

I filtered all rows where [Name] is blank.  I made the assumption that those are Total rows.  Other criteria could just as easily be used.  I also removed [Revenue against Prorated Goal] from the source.

After 4 simple measures and moving the sub-totals to below the detail instead of above, your matrix is complete.

Matrix Grouping.pbix

Let me know if you have any questions.

[lw24]

I'm on a work computer and using work credentials. Not sure I'm able to download this... everytime I click the link it takes me to a One Drive page with an error... 

[lw24]

There's no file in One Drive when I click on the link. 

I get a message that says ...

[gmsamborn]

That looks like a firewall at your end.

Does this work?

https://www.dropbox.com/scl/fi/5msskcifkfcht3slawmc0/Matrix-Grouping.pbix?rlkey=55qjf8tnoludb48w2y6n...

or this?

https://we.tl/t-EcfCx7uRQi

[lw24]

I am able to access and initiate the download. However, I'm not able to open because I don't have the latest version of Power BI (which i'm suppose to download from a specific location). My version 2.118.828.0

[gmsamborn]

Hi @lw24 

You can get the latest version here.

https://www.microsoft.com/en-us/download/details.aspx?id=58494

Also, can you include @gmsamborn  in your reply or I might not see it for a while.

[lw24]

@gmsamborn are screenshots possible? 

[gmsamborn · Accepted solution]

@lw24 

How about this?  Go into Power Query (in a blank pbix) and create a blank query.  In Advanced Editor, replace the existing code with:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WciwuTi1RCE9NzCnJUPBNzEtMT81NzStR0lHyyk9VCM7NLMkAshVUFMDA0MBAx8DAAEkEAUwxpUxgQhYGqkqxOvhs881MzkhMzVGIzC/NS0cyxAinjdjcYggTMoRZGO6rEJJfkpgDUoak1Bi3sVj8ARcyNoYY65SYl50JdqdTUWlyqkJ4YmVeKhXDCWG+b5GegltRampVKvUCBWo6LQLGvSgxLwWrwWY4Dca0EuELsLmxAA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        Sector = _t,
        Name = _t,
        #" Goal " = _t,
        #" Prorated Goal " = _t,
        #" Revenue " = _t,
        #"Revenue against Prorated Goal" = _t
      ]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"Sector", type text},
      {"Name", type text},
      {" Goal ", Currency.Type},
      {" Prorated Goal ", Currency.Type},
      {" Revenue ", Currency.Type},
      {"Revenue against Prorated Goal", Percentage.Type}
    }
  ),
  #"Removed Columns" = Table.RemoveColumns(#"Changed Type", {"Revenue against Prorated Goal"}),
  #"Replaced Value" = Table.ReplaceValue(
    #"Removed Columns",
    " ",
    "",
    Replacer.ReplaceValue,
    {"Name"}
  ),
  #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Name] <> "")),
  #"Renamed Columns" = Table.RenameColumns(
    #"Filtered Rows",
    {{" Goal ", "Goal"}, {" Prorated Goal ", "Prorated Goal"}, {" Revenue ", "Revenue"}}
  )
in
  #"Renamed Columns"

Exit Power Query.

Next, create these 4 measures:

Goal = SUM( 'FactTable'[Goal] )

Prorated Goal = SUM( 'FactTable'[Prorated Goal] )

Revenue = SUM( 'FactTable'[Revenue] )

Revenue against Prorated Goal = DIVIDE( [Revenue], [Prorated Goal] )

Next, create a matrix visual and populate it as follows:

Rows: 'FactTable'[Sector], 'FactTable'[Name]

Values:  the 4 measures you just wrote

Then its just a matter of setting "+/- icons" to Off and "Stepped Layout" to Off (depending on your preference).  Those settings can be found on the Format pane, under Row Headers (and Row Headers --> Options).

[lw24]

I'm not suppose to download applications from outside my company's specific environment. 

[gmsamborn]

@lw24 

I guess your hands are tied.  I would try to explain that it is software required for you to do your job.