Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Re: How to calculate a turnover with the first pri...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

jopezzo

Helper I

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-07-2019
02:35 AM

Hi! I am trying to calculate a turnover not based on the price that appears on each row of my fact table, but based on the first price found.

Example:

Month | Quantity | Price | Turnover |

1 | 145 | 1,187 | 172,115 |

2 | 149 | 1,187 | 176,863 |

3 | 182 | 1,187 | 216,034 |

4 | 155 | 1,187 | 183,985 |

5 | 111 | 1,187 | 131,757 |

6 | 128 | 1,187 | 151,936 |

7 | 105 | 1,187 | 124,635 |

8 | 142 | 1,187 | 168,554 |

9 | 118 | 1,163 | 137,234 |

10 | 162 | 1,163 | 188,406 |

11 | 175 | 1,163 | 203,525 |

12 | 155 | 1,163 | 180,265 |

2,035,309 |

For the turnover, I would have something like:

Turnover =

SUMX(fact_Volumes,fact_Volumes[Price]*fact_Volumes[Quantity])

What I would like is a measure to calculate the turnover with the actual quantity and the first price appearing in the table. In this example I would have all the quantities multiplied by the price 1,187.

Any idea?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

HotChilli

Super User I

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-14-2019
04:27 AM

Thanks for the data.

I changed the formula to take different Material into account.

Create a column

EarlyPrice Revenue = VAR _CurrMaterial = FactPrices[Material] RETURN FactPrices[Quantity] * CALCULATE(SUM(FactPrices[Price]) , FILTER(FactPrices, FactPrices[Material] = _CurrMaterial && FactPrices[DateKey] = MIN(FactPrices[DateKey]) ) )

10 REPLIES 10

Highlighted
##

HotChilli

Super User I

Re: How to calculate a turnover with the first price found in a table?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-07-2019
07:13 AM

You could add a column. I'm not sure if SELECTCOLUMNS function is better or worse than LOOKUPVALUE in terms of efficiency but it should get you the answer

Column 2 = VAR _minRow = TOPN(1, Table4, Table4[Month], 1) VAR _firstPrice = SELECTCOLUMNS(_minRow, "pr", Table4[Price] ) RETURN _firstPrice * Table4[Quantity]

Highlighted
##

jopezzo

Helper I

Re: How to calculate a turnover with the first price found in a table?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-07-2019
07:45 AM

Thanks!

I am still getting an error: "A table of multiple values was supplied where a single value was expected."

Last row doesn't accept a column. Any idea?

Highlighted
##

HotChilli

Super User I

Re: How to calculate a turnover with the first price found in a table?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-07-2019
07:47 AM

Is that with the sample data provided or your real data?

Highlighted
##

jopezzo

Helper I

Re: How to calculate a turnover with the first price found in a table?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-07-2019
07:50 AM

With the real data.

Highlighted
##

HotChilli

Super User I

Re: How to calculate a turnover with the first price found in a table?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-07-2019
07:52 AM

OK, do you want to provide a more realistic sample of data? It doesn't have to be real (if the data is confidential) but obviously the real data is more complex

Highlighted
##

jopezzo

Helper I

Re: How to calculate a turnover with the first price found in a table?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-13-2019
06:33 AM

Hi,

Let's take a more realistic example. I have a table FactPrices:

This table is related to dimDates with the dimension DateKey:

I want to calculate the total revenue based on the first price found for each Material.

What I want to achieve, in this example, is to calculate (12 (first price for Product 1) * 6535) + (17(first price for Product 2) * 6320).

I tried to create the following calculated table:

Summarised Table =

SUMMARIZE (

FactPrices,

FactPrices[Material],

dimDates[DateKey],

"Date",FIRSTDATE(dimDates[Date]),

"Quantity", SUM ( FactPrices[Quantity] ),

"FirstPrice", CALCULATE ( AVERAGE ( FactPrices[Price] ), FIRSTDATE ( dimDates[Date] ) )

)

When doing so, I thouht I would be able to keep only the first prices, while calculating the sum of quantities per Material. Instead, I still get all the prices.

What am I doing wrong?

Highlighted
##

HotChilli

Super User I

Re: How to calculate a turnover with the first price found in a table?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-13-2019
07:24 AM

Could you post the data (not a picture) , so that i don't have to type it in. In a similar way to how you posted the initial data.

Or link your pbix.

Thanks

Highlighted
##

jopezzo

Helper I

Re: How to calculate a turnover with the first price found in a table?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-14-2019
03:14 AM

Highlighted

HotChilli

Super User I

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-14-2019
04:27 AM

Thanks for the data.

I changed the formula to take different Material into account.

Create a column

EarlyPrice Revenue = VAR _CurrMaterial = FactPrices[Material] RETURN FactPrices[Quantity] * CALCULATE(SUM(FactPrices[Price]) , FILTER(FactPrices, FactPrices[Material] = _CurrMaterial && FactPrices[DateKey] = MIN(FactPrices[DateKey]) ) )

Announcements

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Top Solution Authors

User | Count |
---|---|

42 | |

35 | |

25 | |

15 | |

15 |

Top Kudoed Authors

User | Count |
---|---|

51 | |

51 | |

27 | |

22 | |

20 |