CODEWITHSUNDEEP
powerbidaxpowerpivot
Tifsta
Tifsta

Reputation: 21

Calculate price based on distinct count

I am having trouble working out a measure (Revenue) in power bi.

I have a measure which is basically counting distinct values in a table (table 1). From this column I want to multiply the distinct count to get the total price (prices are in another table).

See below for an example

Table 1 

Product DistinctCount Revenue (Measure I am trying to Calculate)
A           15         45.00
B           30         60.00

Prices Table

Product Price
A         3.00
B         2.00

At the moment the Revenue is calculating based on COUNT and not DISTINCTCOUNT.

Any help would be much appreciated. thanks!

Measures, Calculated Columns, Google

Upvotes: 0

Views: 934

Answers (1)

StelioK
StelioK

Reputation: 1781

I am assuming you have a relationship set up between these two tables on [Product]. If this is the case you can do something like this to create a calculated column: 

Revenue =
CALCULATE (
    SUMX ( 'Table 1', 'Table 1'[DistinctCount] * RELATED ( 'Prices Table'[Price] ) )
)

If you are trying to create a table visual try the DAX below, where ID is just a transaction ID for each product in your 'Table 1':

Revenue =
VAR DistinctCountOfProductTransactions =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Id] ) )
VAR Result =
    CALCULATE (
        DistinctCountOfProductTransactions * SUM ( Prices[Price] ),
        TREATAS ( VALUES ( 'Table'[Product] ), Prices[Product] )
    )
RETURN
    Result

Upvotes: 0

Related Questions