Forum Discussion

Emranit's avatar
Emranit
Icon for Helper II rankHelper II
9 months ago
Solved

Regarding help for power BI DAX

https://docs.google.com/spreadsheets/d/1ZpK2aoR7Xo2Nnz4WfqAR0MJS1aWfBkJT/edit?usp=sharing&ouid=113914284006601104314&rtpof=true&sd=true Please check the link file firstly,   Each Day/Date one Mach...
Need Help
  • danextian's avatar
    danextian
    9 months ago

    Hi Emranit 

     

    You can use a measure to virtually summarize a table then multiple  virtual capacity column by 2.5, assuming there's only capacity value per day per machine. 

    Machine Capacity =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[Date], 'Table'[Machine Name], 'Table'[Capacity] ),
        "@machine capacity", [Capacity] * 2.5
    ),
    [@machine capacity]
)

    Otherwise you'll need to calculate the max capacity per machine per day virtual before multiplying it by 2.5

    Machine Capacity2 =
SUMX (
    // Iterate over each unique Date–Machine combination
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[Date], 'Table'[Machine Name] ),
        
        // Add a computed column for capacity × 2.5
        // CALCULATE is needed so MAX('Table'[Capacity]) is evaluated
        // in the filter context of each Date–Machine row produced by SUMMARIZE.
        "@machine capacity",
            CALCULATE( MAX( 'Table'[Capacity] ) ) * 2.5
    ),

    // Sum all values of the computed column
    [@machine capacity]
)

     

alish_b's avatar
alish_b
Icon for Super User rankSuper User
9 months ago

Hey Emranit ,


Let's start with the simplest method and then add some complexity:
1. Assuming you will show this in a table visual by pulling in Date and Machine Name in the table as well, the following will simply work (replace the 'Table' with your Table name):

Machine Capacity = MIN('Table'[Capacity]) * 2.5
How this works is that the rows in the table visual will provide sufficient filter context to the measure. So in a row in the table visual that has say 12/03/2025 and BD04, it will only look at records in the actual data table related to these two, then calculate the Minimum capacity which will be 400 since all the capacity of BD04 will be same that is 400 minimum or maximum will also be 400. And then multiplication by 2.5

Now the totals will get messed up though, it will show the capacity * 2.5 for the lowest capacity machine, because no date or machine filter will be there in the Total row. Okay now you could go three ways from:
1. Don't need the totals at all, just turn it off in the Visual settings. (Visual>Totals>Turn off)
2. Don't need to show totals for this particular Machine capacity field but would like to show the capacity for other fields say, Production, then you will hide the total value with DAX:
Machine Capacity = IF( HASONEVALUE('Table'[Machine]), MIN('Table'[Capacity]) * 2.5, BLANK() )
3. Or in the totals you want to show the sum of all machine capacities multiplied by 2.5 then:
Machine Capacity =
SUMX(
    VALUES('Table'[Machine Name]),
    CALCULATE(MIN('Table'[Capacity])) * 2.5
)


Hope it helps!
Emranit's avatar
Emranit
Icon for Helper II rankHelper II
9 months ago

Not working properly. I have got the solution. Thanks for your nice cooperation.