Forum Discussion

alexbalazsalex's avatar
alexbalazsalex
Helper IV
8 years ago
Solved

Urgent Support for matrix table

  Dear all .. not very good with Power Bi and need your support to re create the above table in matrix table :   Bellow is may data :   Year_Month Product Customer Real PCS Bdg PCS ...
Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi alexbalazsalex,

     

    You can do the grouping base on the column in two ways:

     

    1. Advance Query Editor

    • Add conditional column and place the following code:

     

    2. In front end

    • Select the column you and and right click or ... and choose New group
    • On the next window select the value to group and choose the names
    • Then add this to the columns in the matrix.

     

    In attach is the PBIX file for download (this is a WeTransfer and it's only available 7 days)

     

    Any further questions please tell me.

     

    And sorry for the delay.

     

    Regards,

    MFelix 

     

     

MFelix's avatar
MFelix
Super User
8 years ago

Hi alexbalazsalex,

 

You should create the  measure has you describe howver if you want to have the values by 1000 you should also create the measure for the invoicing values and the quantities not columns.

 

This way you can use those measure to calculate other measures so you would have something like this:

 

 

Invoicing USD = SUM('Fact'[Invoicing US$])/1000000

Budget USD = SUM('Fact'[Invoice BDG US$])/1000000

Make the same for pieces

Then use it on the other measures like:

Invoicing vs Budget = [Invoicing USD]-[Budget USD]

 

Then just add to your matrix visual in the values

 

Regards,

 

MFelix

 

  • alexbalazsalex's avatar
    alexbalazsalex
    Helper IV
    8 years ago

    Hi Felix

     

    thanks for the reply ,

     

    any ideas how i should add in the matrix the columns,

     

    to show

     

    Metrics like Invoicing USD Mill and then under real vs Bdg.

    Volume Th Pcs real than real vs BDg ... this is my main doubt

    • alexbalazsalex's avatar
      alexbalazsalex
      Helper IV
      8 years ago

      Hi MFelix,

       

      Any ideas on how to proceed ?

       

      Many Thanks

      Alex

      • alexbalazsalex's avatar
        alexbalazsalex
        Helper IV
        8 years ago

        Dear All,

         

        Could you please advise ? Getting nowhere unfortunalty ...

         

        Thanks

    • MFelix's avatar
      MFelix
      Super User
      8 years ago

      Hi alexbalazsalex,

       

      In PBI it's possible to make an hierarchy between columns so the visual look you need would appear as you want with a first aggregated level and then a second level with the sub-value, however this is only possible on columns levels so you can create different hierarchy so in the matrix visuals those would look like you need to have them.

       

      At the measure level that is not possible so if you add a measure to your data it will be repeated at all the levels of your matrix.

       

      A workaround can be:

      • Unpivot you data columns (PCS, VALUES, ...)
      • Add a custom column with an IF statment classifiying all of the columns
        • Real PCS + BGD Pieces would be in Volume Pieces
        • ....
      • Make measure based on a single colum something like this:
      • PCS vs Budget = CALCULATE(SUM(Data_Table[Value]); Data_Table[Type] = "Real PCS") - CALCULATE(SUM(Data_Table[Value]); Data_Table[Type] = "Bdg PCS")
      • Then add the measure on your Matrix
      • Where the measure are null just reduce the size until it's hidden.

       

       

       

      This is just an high level explanation sorry for not getting this into detail will try to have a complete guide during this evening with full step by step with images.

       

       

       

      This will change the way you calculate you measures since the information is on a different format than you have.

       

      Hope this helps.

       

      Regards,

      MFelix

      • alexbalazsalex's avatar
        alexbalazsalex
        Helper IV
        8 years ago

        Hi MFelix

         

        Starting to look good i think.. can you share the file with me so i can check the if formula you used and others ?

         

        Many Thanks

        Alex