Forum Discussion

icdns's avatar
icdns
Post Patron
4 years ago
Solved

Per Unit Computation Dax

Hi Guys,    Would need your help in constructing a dax that computes the per unit computation. The computation of Per Unit is (Present value) ÷ (Maximum Data Point) I'm using a date hierarcy (Year,...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi icdns ,

     

    I download you sample and have test. Firstly I suggest to to sort [Month] column by [MonthNo] column.

    For reference: Sort one column by another column in Power BI Desktop

    Then create a measure as below to achieve your goal.

    Sales Percentage = 
VAR _SUMMARIZE =
    SUMMARIZE (
        ALLSELECTED ( sampledata ),
        sampledata[Year],
        sampledata[Month],
        sampledata[City],
        "Sales", CALCULATE ( SUM ( sampledata[Sales] ) )
    )
VAR _ADDMAX =
    ADDCOLUMNS (
        _SUMMARIZE,
        "MaxSales", MAXX ( FILTER ( _SUMMARIZE, [City] = EARLIER ( [City] ) ), [Sales] )
    )
VAR _Sales =
    CALCULATE ( SUM ( sampledata[Sales] ) )
VAR _MaxSales =
    MAXX ( FILTER ( _ADDMAX, [City] = MAX ( sampledata[City] ) ), [MaxSales] )
RETURN
    DIVIDE ( _Sales, _MaxSales )

    Result is as below.

     

    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.

      

Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

Hi,

I hope you can get an idea on how to create a measure from the below picture and the attached pbix file.

 

 

Per unit measure: =
VAR maxcount =
    CALCULATE ( MAX ( Data[Count] ), REMOVEFILTERS ( Data ) )
RETURN
    DIVIDE ( [Count measure:], maxcount )