Get sum of rows based on multiple criteria and relationships

[ediaz795]

Hello,

I'm getting my first project on PBI after my company gave me licence.

In a nutshell, I have 2 numbers: Forecasted Time and Actual Time. Sometimes we get bigger actual than forecasted, and viceversa. So I need to measure the Actual Time % Forecasted (we internally call it "Allocation"). The data it's given by Day of Week and half-hour intervals.

Below an sketch that we made on Excel:

The DAX Measure for Actual Time % Forecasted is this:

=IF(
    ([Sum of ActualMinutes]/[Sum of ForecastedMinutes]) <= 0, 
          0,
    [Sum of ActualMinutes]/[Sum of ForecastedMinutes]
)

Also, we measure the trend of how the numbers move with the following table:

The DAX measure for Actual Time % Forecasted Trend is this: 

=CALCULATE([Actual_Time_%_Forecasted],

     FILTER(ALLSELECTED('Calendar'[Day Of Week Number]), 'Calendar'[Day Of Week Number] <= MAX('Calendar'[Day Of Week Number])),

     FILTER(ALLSELECTED(Interval[Interval]), Interval[Interval] <= MAX(Interval[Interval]))

)

Now, on Power BI, this is the model that I've been working on

I have the datasets the sources connected, the indexes for Day of Week and Half-Hour intervals and category for our data. And I'm stuck in here, I'm not able to mimic the DAX formulas that we use on the Excel prototype on Power BI DAX measures.

I think this is so basic and dummy, but I hope to learn much about it.

Any ideas how can I solve this?

PS. on the Excel book, we have slicers to filter in/out the data that we are analyzing , but I think after solving the above, I'll be able to add filters or buttons to the Power BI Report

[daxer-almighty]

@ediaz795 

If you have a working power pivot model in Excel, then how come you are not able to replicate such a model in PBI? Where's the problem? Because despite all the info I can't see it... except that your measures are not optimal and in fact too rigid since they only work correctly with certain attributes on the axes, whereas they should work correctly with any attribute that comes from the time-related dimensions.

For instance, why do you think that

([Sum of ActualMinutes]/[Sum of ForecastedMinutes]) <= 0, 

could be less than 0? Are the two measures not non-negative integers?

Secondly, you are calculating an expression twice in the first measure instead of once by using a variable.

Thirdly, the second measure should be written in a different way using ALLSELECTED as a CALCULATE modifier, not as a table function. The way it's written also bans proper calculations on the full date/time axis.

Many things to attend to here but I'd really want to know why you can't replicate a working PP model from Excel.