CODEWITHSUNDEEP
powerbidaxpowerpivot
Ilshat Khamitov
Ilshat Khamitov

Reputation: 1

POwer pivot Calculated field using aggregates

I have a source table loking like this

Date Measure Name Measure value
01/01/2020 Revenue 1250
01/01/2020 Sales number 43
01/01/2020 Costs 1000
01/02/2020 Revenue 4500
01/02/2020 Sales number 590
01/02/2020 Costs 3420

I create a power pivot table, putting measure name column in ROW field of Power Pivot, which gives the following result :

Jan Feb
Revenue 1250 4500
Costs 1000 3420
Sales number 43 590

Now I need to calculate Revenue per sale (revenue/sales number). But since revenue and sales number are presented in a single column in source table, they are not available for calculation. How can I solve this using DAX? I understand that this could be solved using power query, but that is not a preferred method.

Upvotes: 0

Views: 257

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9062

With a new measure, perhaps:

Revenue per Sale :=
VAR Revenue =
    CALCULATE ( SUM ( Table1[Measure value] ), Table1[Measure Name] = "Revenue" )
VAR Sales =
    CALCULATE (
        SUM ( Table1[Measure value] ),
        Table1[Measure Name] = "Sales number"
    )
RETURN
    DIVIDE ( Revenue, Sales )

Upvotes: 0

Related Questions