cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
calerof
Impactful Individual
Impactful Individual

Include several mesasures into one table

Hello,

I'm trying to perform the easiest process in Excel where you take several measures and put them together into one table like this:

tableInExcel.png

The first Total in column P is a measure with this code:

 

NoTRSDerTotal = 
VAR MinXShift = [Sum MinLab] * [Count Num Days]
RETURN
DIVIDE(
    [Sum MinNoTRSDer],
    MinXShift
    )

 

where NoTRSDerTotal is the number of minutes the manufacturing machine was not operating divided by the total minutes per shift.

MinLab are the total working minutes per shift, or the working hours per shift * 60:

 

Sum MinLab = SUM(HorasxTurno[MinLaborables])

 

minLab.png

CountNumDays is the number of days where there were operations.

MinNoTRSDer are the number of minutes the manufacturing machine was not operating due to a failure, which are classified with the code NoTRSCode that can be AMOL, AP, CM, PO, PP.

 

Then, I have two additional measures:

1) TRSDer%

 

TRSDer% = 
DIVIDE(
    CapturaProduccion[TUDer],
    [Sum Horas Laborables] * [Count Num Days]
)

 

 

where TRSDer% is the coefficient of TUDer divided by the total hours per shift times the number of working days.

 

TUDer = 
SUMX(CapturaProduccion, DIVIDE( [Sum PzProdBuenasDer], CapturaProduccion[PzObjDer] ) )

 

 

2) NoCal

 

_NoCal%Der = 
DIVIDE(
    SUMX(CapturaProduccion, DIVIDE( [Sum PzScrapDer], CapturaProduccion[PzObjDer] ) ),
    [Sum Horas Laborables] * [Count Num Days]
)

 

where NoCalDer divides the total bad pieces by the target number of pieces per shift as numerator, and divides this by the total number of hours per shift times the number of working days.

 

What I need is to show all in one table to add them up and show as a visual in a chart.

Now I just have them sitting in two different tables in PBI:

measure1 NoTRSmeasure1 NoTRSmeasure2 TRSmeasure2 TRSmeasure3 NoCalmeasure3 NoCal

I know it looks a bit complicated but in excel is so easy. Here I'm really struggling to think on a solution.

Here is my pbix file.

I appreciate a lot any help on this.

Thanks,

Fernando

1 ACCEPTED SOLUTION
calerof
Impactful Individual
Impactful Individual

I'm closing this post and will check the data model and the fact table to improve it. Thank you for any help provided.

Fernando

View solution in original post

7 REPLIES 7
Pragati11
Super User
Super User

Hi @calerof ,

 

I would say it's easy in Power BI as well. 🙂

 

So measures are never at a table/dataset level. They are at a data model level. You can access any measure from any table in other table.

 

So basically, in TABLE1 you can create a measure that adds up all the measures coming from TABLE2 and from TABLE3.

 

Hope this helps.

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




calerof
Impactful Individual
Impactful Individual

Hi @Pragati11 ,

Thanks for your response. But I need the granularity, I need to show basically each column from the first table, i.e. NoTRS with each category: AMOL, AP, CM, etc, and TRS and NoCAL, and then the Grand Total. 

F

 

RicoZhou
Community Support
Community Support

Hi @calerof 

I think you want to show two matrix visuals in one like Excel.

You can build a new table (By merge)then pivote them or build five measures (AMOL, AP, CM, PO, PP and add these measures like NoTRSDerTotal, TRSDer% into values in matrix).

You may refer to this post: How to Subtract Data From Same Column In Imported Table When Put In Matrix?

 

Best Regards,

Rico Zhou

 

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

 

 

calerof
Impactful Individual
Impactful Individual

@RicoZhou Thank you for your response. Let me try it and I'll come back with my feedback.

Best regards,

Fernando

RicoZhou
Community Support
Community Support

Hi @calerof 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

 

Best Regards,

Rico Zhou

calerof
Impactful Individual
Impactful Individual

My pbix file is in my first post.

calerof
Impactful Individual
Impactful Individual

I'm closing this post and will check the data model and the fact table to improve it. Thank you for any help provided.

Fernando

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors