Creating new table adding a custom column to summarize

[Victormar]

Hi again community,

I've been trying for a while to create a new table from some existing columns that I have, but also adding a column manually, and I haven't been able too. 

I have tried with summarize, summarize columns, selected columns... but I am not able to create what I want.

The case is that I have a table with vehicles, and I have 9 columns for mileage, speed and energy deviation, each one of them could be ok, over, or below, so 3x3 basically.

That said, I would like to create a new table with 4 columns, like this:

Although here I am missing a column that would be called Measure, first row would be Mileage, 2nd Speed, 3rd Energy. 

This is the code I have used:

I think the problem is that I have no way to relate the columns to the measure itself to put it into rows. 

I am doing this because I want to create a matrix similar to the first table, where the rows are the measures and the columns the values for "ok", "over" and "below".

Hope that makes sense, thanks in advance for you time 🙂

[Sharonsb]

Hi,

Please help with the following question:

I would like to have a new column by summarizing the rows from one column in the same table along with the corresponding . This is the table format 

Project  FY22 FY23 FY24 FY25
abc      100 200 300 400
def        500 600 700 800
ghi      900 100 200 300
jkl       400 500 600 700
mno   800 900 100 200

I would like to achieve the following :

FY22 FY23 FY24 FY25
abc+ghi+mno 1800 1200 600 900
def+jkl             900 1100 1300 1500

[v-jianboli-msft]

Hi @Victormar ,

What does the fleet_id look like? What is the logic of filtering? Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[Victormar]

Hello! I did a workaround that is working for now, just creating a matrix with the values. My idea was too much tailored and it would have been difficult to maintain in the long run.

Many thanks for your time and help 😄

[v-jianboli-msft]

Hi @Victormar ,

Have you considered creating a relationship between the two tables?

Besides, this question is beyond the topic at the beginning of this thread. If this advice doesn't work, please consider about mark the answer to this post as a solution and create a new thread about the new issue, which will better help other users.

Best Regards,

Jianbo Li

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

[Victormar]

Hello, and thanks again for your answer.

You are right. Although as an extension it might be worth it to take it here, not to spam again with the explanation?

@v-jianboli-msft , How would you link the tables though, I was trying in the meantime to add the fleet_id to the table, but didn't succeed, since SUMMARIZECOLUMNS doesn't allow it.

Cheers!

[v-jianboli-msft · Accepted solution]

Hi @Victormar ,

Please try:

Table = 
UNION (
    SUMMARIZECOLUMNS (
        'DIM_Chassis',
        "Name",("Mileage"),
        "OK", COUNT ( DIM_Chassis[c_MileageLateastIsDeviatedGreen] ),
        "Over", COUNT ( DIM_Chassis[c_MileageLateastIsDeviatedRed] ),
        "Below", COUNT ( DIM_Chassis[c_MileageLateastIsDeviatedYellow] )
    ),
    SUMMARIZECOLUMNS (
        'DIM_Chassis',
        "Name",("Speed"),
        "OK", COUNT ( DIM_Chassis[c_SpeedLateastIsDeviatedGreen] ),
        "Over", COUNT ( DIM_Chassis[c_SpeedLateastIsDeviatedRed] ),
        "Below", COUNT ( DIM_Chassis[c_SpeedLateastIsDeviatedYellow] )
    ),
    SUMMARIZECOLUMNS (
        'DIM_Chassis',
        "Name",("EnergyConsumption"),
        "OK", COUNT ( DIM_Chassis[c_EnergyConsumptionLateastIsDeviatedGreen] ),
        "Over", COUNT ( DIM_Chassis[c_EnergyConsumptionLateastIsDeviatedRed] ),
        "Below", COUNT ( DIM_Chassis[c_EnergyConsumptionLateastIsDeviatedYellow] )
    )
)

Final output:

Best Regards,

Jianbo Li

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

[Victormar]

Hello!

Thanks for the reply, it works! I am wondering now how can I relate that to the fleet_id that I have in another table.

I see that this way I have a table that summarizes the numbers, but cannot use it to be filtered.

Is there any way to connect it to the other table with fleet_id?

Thanks in advance!