Forum Discussion

RoadeoAnalytics's avatar
RoadeoAnalytics
Advocate I
3 years ago
Solved

Power BI DAX Calculation

Hi,   I would like some help figuring out how to derive at revenue per driver for the attached dataset. I've gotten this to work on a high level, but when it's filtered by driver, it returns blank....
Need Help
  • RaocoSolutions's avatar
    RaocoSolutions
    3 years ago

    RevenueDriverPU =
    VAR _avgPU = Calculate(AVERAGE('All P&D Tables'[Rate$/PU #PKG]),ALL('All P&D Tables'[DRIVER NAME]))
    return
    SUM('All P&D Tables'[PU #PKG AI]) * _avgPU

AndrewPF's avatar
AndrewPF
Helper V
3 years ago

Your Rate$/PU #PKG is zero for every occurence of that driver which is why the SUM * AVERAGE is returning nothing. 

If you want to show zero instead of blank, change your measure to: 

RevenueDriverPU = SUM('All P&D Tables'[PU #PKG AI]) * AVERAGE('All P&D Tables'[Rate$/PU #PKG]) + 1 - 1

  • RoadeoAnalytics's avatar
    RoadeoAnalytics
    Advocate I
    3 years ago

    Yes, the Rate$/PU #PKG is zero for every occurance of the driver because these are two different tables where I merged the queries into one table called All P&D Tables.

    The rate comes from the PKG Summary table where I devide PU PKG $AMT by PU #PKG to derive at compensation per package.

    I then want to take this rate and multiply it by the PU #PKG AI field in the Additional Information table to get the revenue for package pickup for each driver. Is there any way to do this?