I am trying to total up prodcode per dcountcustomerid but my totals are not correct. The PCRATE sum should be for each prodcode = pcrate x dcountcustomerID. For prodcode 99202 the pcratesum should equal 47285. It should be for prodcode 99202; pcrate 49.00 x DCOUNTCUSTOMERID 965 = 47,285 but instead it is 49,492. Please advise.
Thanks Jamie. See attached. Some of the totals are incorrect. This data is for 1 month of data and the member id represent a distinct count of member ID’s.
Thanks so much.
Liz
revenuetotalsincorrect.pbix (27.3 KB)
According to your provided pbix, I think the issue was solved.
Thanks for that.
I can see on that PBIX a few are wrong, such as PC 12349, 50 x 99 = 4950, your report is showing 5148.
However I can’t see any DAX calculations or anything, that just seems to be reading off a spreadsheet from your data source so not much can do from my end. I suggest you look at where your data is coming from, if that’s where you’re doing your calculations, as that seems to be the problem.
If you were wanting to use DAX, you could use something like this:
Revenue DAX =
SUM( Sheet2[DCOUNT OF MEMBER ID] ) * SUM( Sheet2[RATE PER PRODUCT CODE] )
Attached is your PBIX with this added.
revenuetotalsincorrect.pbix (27.4 KB)
Hi Jamie,
I’m attaching the powerbi with raw data, which I had to repopulate with fake data.
The thing I am having trouble with is that I need to group by product and then by product
code and per distinct count of member ID. i.e.,
PC PRODUCTCODE DISTINCOUNT MEMBER ID REVENUE (RATEPERPRODUCTCODE X DCOUNT OF MEMBERS:
PC 12345 10 MEMBERS 99.00 = 990.00
UC 12345 5 MEMBERS 49.00 = 245.00
Each product may have same productcode but the rate is different. So I’m trying to see distinctcount of members by product by product code for the revenue.
Anyways, I hope this makes sense.
revenuetotalsincorrect.pbix (27.3 KB)
I believe the PBIX on my previous post should have solved that if I’m understanding it correctly:
revenuetotalsincorrect.pbix (27.4 KB)
If you make a table with the Product Name, Product Code, Distinct Member ID and Rate columns then add the Revenue DAX column, that should be correct.
Hi Jamie,
It’s not working. the numbers are extremely inflated. The totals need to be grouped by product, productcode and the rate per distinct member ID.
Can you open and take a look at the data.
Thanks,
Liz
Can you give me an example of one that is inflated please? I don’t believe I’m understanding your requirements correctly.
This is just a snippet because I can’t share the real data in this forum. I created a measure and the numbers are hugely inflated. Were you able to use your measure in the data I sent?
Yes, that measure is used in the PBIX on my previous reply and it works fine.
This suggests there’s something different in the real data than the ones you’re sending.
On the data you’re sending, it has 1 line per Product Name, Product Code, Rate and Count of Member ID. Does your real data have this across multiple lines, with a line per member ID perhaps that you’re summarising before sending it? If so, then that will cause the numbers to inflate with that DAX I sent, as it’s summing the rate column.
each line represents a member ID (i had to alias title for anonymity) and a code, etc.
Perhaps it is summarizing.
Thanks for your assistance though.
Liz
Hi @lizbethl4, did the response provided by the community members help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!