The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I'm sure this is easy but for some reason I can't solve it.
I have a table.
There is one dimension and two metrics -- Tactic, Visits, Revenue.
I need to create a simple measure that shows revenue per visit.
Tactic 1 has 28,782 visits and $55,565 revenue.
Here is what I get:
Avg. Revenue = sum([revenue]) / sum([Visits] = $3.86 when average is selected
Avg. Revenue = sum([revenue]) / sum([Visits] = $138.94 when sum is selected
When "Do Not Summarize" is selected, the table expands out and does not summarize by tactic.
The correct answer is $1.93 but nothing gives me that answer. Why?
Solved! Go to Solution.
Here is what I have found. I was able to get the right numbers using this formula:
[Final Measure] = Divide([Measure1],[Measure2])
where [Measure1] = sum(value) and [Measure2] = sum(value)
Also, I had to create new measures, not edit existing measures. It would keep giving me the same wrong answers unless I created new measures with the correct formula. Maybe there is something with the way the data is cached.
Thanks for your help, everyone.
Fun stuff!
Add Tactic to the Table - and select Do Not Summarize
And create your Measure as in the image
What is the icon next to your Measure? Could you have created a Column instead of Measure?
Here is what I have found. I was able to get the right numbers using this formula:
[Final Measure] = Divide([Measure1],[Measure2])
where [Measure1] = sum(value) and [Measure2] = sum(value)
Also, I had to create new measures, not edit existing measures. It would keep giving me the same wrong answers unless I created new measures with the correct formula. Maybe there is something with the way the data is cached.
Thanks for your help, everyone.
Fun stuff!
@SHDJason Welcome to my initial pains with PBI. It is an easy calculation to get the anwser you want. You need to create a measure to do this, see the screen shot below of a test page I made:
The DIVIDE function takes away the issue of dividing by 0.
Hope this helps.
Giles
Hi @GilesWalker. Thanks for your help. Here is what I am getting. Still not right.
I'm very confused. This should not be hard.
What are the columns in your table? What is in the Tactic Column?
The tactic column is just a dimension. Nothing complicated.
Ok, check this out:
Total Revenue = sum([revenue)
Total Sessions = sum([sessions])
Revenue per Visit == Rev 2 but the answers are different. Rev 2 is correct. NO idea why.