Transactions Daily from Weekly figure

Anonymous 

Hi,

I have been trying to use your formula but it doesn't allow me to formulate it the way you have it.

Firstly, in the 1st calculate, it doesn't allow me to just add f_DailySales[Store], I had to add a * as it doesn't recognise. If I remove the * this is what appears:

Secondly, on the 2nd Calculate, it doesn't recognise either the F_DailySales[Store]. Even adding * it doesn't recognise as the filter only will read the column Transaction combined, no the other column, F_DailySales.

Any suggestion?

Thanks a lot for your help

Hi alexcatala 

The dax I shown you before is in calculated column.

In your screenshot, you may want to build a measure.

"Earlier" function couldn't be used in measure, we need to use aggregation in measure by function "Sum" or "Max".

You see in my sample , if I build a measure by this dax it will show error as well.

Try this measure.

Daily Transactions in Measure = 
VAR _Sum =
    CALCULATE (
        SUM ( 'Daily sales'[Price] ),
        FILTER (
            ALL('Daily sales'),
            'Daily sales'[Store] = MAX( 'Daily sales'[Store] )
                && 'Daily sales'[Year] = MAX ( 'Daily sales'[Year] )
                && 'Daily sales'[WeekNum] = MAX ( 'Daily sales'[WeekNum] )
        )
    )
VAR _PERCENT =
    DIVIDE ( SUM('Daily sales'[Price]), _Sum )
VAR _Trabsaction =
    CALCULATE (
        SUM ( 'Weekly figures'[Transaction] ),
        FILTER (
            ALL('Weekly figures'),
            'Weekly figures'[Store] = MAX ( 'Daily sales'[Store] )
                && 'Weekly figures'[Year] = MAX ( 'Daily sales'[Year] )
                && 'Weekly figures'[WeekNum] = MAX( 'Daily sales'[WeekNum] )
        )
    )
RETURN
    _Trabsaction * _PERCENT

Result:

For more details about the differences between calculated column and measure you may refer to this blog:  UNDERSTANDING THE DIFFERENCES BETWEEN CALCULATED COLUMNS & MEASURES IN POWER BI

You can download the pbix file from this link: File

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.