Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
alexbalazsalex
Helper IV
Helper IV

Urgent Support for matrix table

to do.PNG

 

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_MonthProductCustomerReal PCSBdg PCSInvoicing US$Invoice BDG US$Margin US$Margin BDG US$
10/1/2017PlanesTK MAXX2.5003.25417.500.00019.351.53812.500.00016.367.620
10/1/2017CarsPEPCO3.2452.4569.735.0006.849.7843.894.0002.846.504
10/1/2017YahtsAUCHAN1.2671.1007.602.0006.422.9003.420.9003.131.700
10/1/2017PlanesTOYS R US4.5673.49731.969.00020.796.65922.835.00017.589.910
11/1/2017CarsTOYS R US9.8089.70729.424.00027.072.82311.769.60011.250.413
11/1/2017CarsAUCHAN54.44350.000163.329.000139.450.00065.331.60057.950.000
11/1/2017YahtsTOYS R US33.42233.400200.532.000195.022.60090.239.40095.089.800
12/1/2017CarsAUCHAN34.56735.000103.701.00097.615.00041.480.40040.565.000
12/1/2017YahtsTOYS R US55.65655.050333.936.000321.436.950150.271.200156.727.350
12/1/2017PlanesPEPCO343.432234.5692.404.024.0001.394.981.8431.717.160.0001.179.882.070
1/1/2018PlanesPEPCO3.4534.53524.171.00026.969.64517.265.00022.811.050
1/1/2018CarsTK MAXX32.43220.00097.296.00055.780.00038.918.40023.180.000
1/1/2018YahtsTK MAXX2.3453.23214.070.00018.871.6486.331.5009.201.504
11/1/2017PlanesTOYS R US23.43523.245164.045.000138.238.015117.175.000116.922.350
11/1/2017PlanesPEPCO34.23435.000239.638.000208.145.000171.170.000176.050.000
10/1/2017CarsTK MAXX43.43544.000130.305.000122.716.00052.122.00050.996.000
11/1/2017PlanesAUCHAN432.323440.0003.026.261.0002.616.680.0002.161.615.0002.213.200.000
11/1/2017YahtsPEPCO233.434230.0001.400.604.0001.342.970.000630.271.800654.810.000
11/1/2017CarsTK MAXX2.243.4322.340.0006.730.296.0006.526.260.0002.692.118.4002.712.060.000
12/1/2017PlanesTOYS R US24.34224.452170.394.000145.416.044121.710.000122.993.560
11/1/2017YahtsTK MAXX2422001.452.0001.167.800653.400569.400
12/1/2017PlanesPEPCO4.2244.00029.568.00023.788.00021.120.00020.120.000
12/1/2017PlanesTK MAXX2.342.4232.345.00016.396.961.00013.945.715.00011.712.115.00011.795.350.000
12/1/2017CarsTOYS R US34.24233.000102.726.00092.037.00041.090.40038.247.000
12/1/2017CarsPEPCO24.23323.23072.699.00064.788.47029.079.60026.923.570
1/1/2018CarsAUCHAN24.32432.32472.972.00090.151.63629.188.80037.463.516
12/1/2017YahtsTOYS R US54.45455.000326.724.000321.145.000147.025.800156.585.000
12/1/2017PlanesTK MAXX53.45347.000374.171.000279.509.000267.265.000236.410.000
1/1/2018PlanesTOYS R US3244002.268.0002.378.8001.620.0002.012.000
1/1/2018YahtsTK MAXX235.235230.0001.411.410.0001.342.970.0005.005654.810.000
1/1/2018CarsTOYS R US2.3422.5007.026.0006.972.5002.810.4002.897.500
1/1/2018PlanesTK MAXX2.3542.40016.478.00014.272.80011.770.00012.072.000
1/1/2018YahtsAUCHAN2343451.404.0002.014.455631.800982.215
1/1/2018CarsTOYS R US2.3442.3247.032.0006.481.6362.812.8002.693.516

 

-For Price US$/Pcs create a measure ? Sum(Invoicing US$)/Sum(Real Pcs) the same for BDG

 sum(Invoice BDG US$)/sum(Bdg Pcs).

Delta new column + Real US$/Pcs-BDG Pcs

Same for Gross MArgin:

-Weight of Margin on invoicing :

Measure: MArgin US$/Invoicing US$ - same for BDg and Delta to be difference.

-TO show in Table Th Pcs - Do we have to create a new column with new calculation ? Real Pcs/1000?

Mill USD Table Th Pcs - Do we have to create a new column with new calculation ? Invoicing US$/1000000.

 

The Visualisation on dashbord is important to show the same info as shown.

 

Please Help

 

Many Thanks

1 ACCEPTED SOLUTION

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:

query_editor.png

 

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.

Grouping.png

 

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 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

14 REPLIES 14
MFelix
Super User
Super User

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi Felix

 

thanks for the reply ,

 

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

 

to show to do.PNG

 

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

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

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.

 

Matrix.gif

 

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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:

query_editor.png

 

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.

Grouping.png

 

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 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix

 

i checked your option ... but i ve seen that you multiplied each row by type for each date ... in my case in a db of 100k rows ... it would be difficult..

 

Do you think there is other way around it ? or just this way ?

 

Many Thanks.

Alex

Hi @alexbalazsalex,

Don't understand your question. The unpivot of columns will make a single row for each attribute (is what you refer as multipying??) because as said before you can't have the data with a title and subtitle within measures.

In terms of performance by the PBI model you should not have big issues since everything is done when refreshing the model, again this is just a workaround to give the title/ subtitle since you can't use measures in the hierarchy.

Regards,
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix,

 

thanks for your patiente and suport ..

 

And this unpivoting can be done stragit in Power bi ? or have to be done before as i ve seen tha tyou have "Type" column in you attached fiel already ..

 

 

Thanks Alex

Hi @alexbalazsalex,

 

This unpivoting is done in the query editor.

 

If you go to the PBIX file I shared you can check how it works.

 

Any questions please tell me

 

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix,

 

Any ideas on how to proceed ?

 

Many Thanks

Alex

Dear All,

 

Could you please advise ? Getting nowhere unfortunalty ...

 

Thanks

Up

Up

Hi @alexbalazsalex,

 

Today I'm dealing with some urgent work related issues. Will get into this later this evening and will try to provide an answer ASAP.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.