Divide a filtered value by another filtered value

[Alessandro-laba]

Hi all, 

I am devoloping a dashboard to see how many downloads we had about a specif .pdf regarding a specific event. Additionally I want to show how many of these downloads come from the Webisite, LinkedIn and Email. Unfortunately I am stuck with this latest point cause I can't manage to get the right formula. 

Please see the below set of data: 

 

As you can see I have 2 events. By Source I mean from where the .pdf was downloaded and the ID is the unique id of the contact that downloaded the report (please be aware that in the real environment I don't have the ID nut the email address).

Now what I would like to have for Event1 would be a graph that shows the below stats when I filter by Event1:

Email: 18.18%

LinkedIn: 45.45%

Website: 36.36%

Does anybody know a way to achieve this?

Thanks,

Ale

[dm-p · Accepted solution]

Hi @Alessandro-laba,

I've provided you with a solution below, and I've created a simple workbook for you to explore further if you want to play around.

Just as a heads-up: if you have any questions like this in future, it's probably best to start in either the DAX or Desktop forums, as they have a lot of users actively monitoring threads and can help much faster than in here. We don't get a lot of traffic within this particular forum. It's typically geared towards extension of Power BI through software development tools.

Onto your solution! 🙂

I've replicated your data into a table called Data for this example, so this is the object name the measures will use.

First of all, we need our numerator, so we create a measure for this, e.g.:

# Downloads = COUNTROWS(Data)

We can check this works as we expect with our slicer:

Now, we need to get the denominator. We create a new measure for this, e.g.:

# Total Downloads for Event = 
    CALCULATE(
        [# Downloads],
        ALLEXCEPT(Data, 'Data'[NameOfTheEvent])
    )

In this case, we re-use our above measure but remove the filter on the table, except for the NameOfTheEvent column using the CALCULATE and ALLEXCEPT DAX functions. This effectively groups the measure by that column. We can check this again in our table and slicer, e.g.:

Now that we know that choosing by event gives us the correct numerator and denominator, we can just create a measure that divides them and then format that as a percentage, e.g.:

% Downloads by Source = 
    DIVIDE(
        [# Downloads],
        [# Total Downloads for Event]
    )

We can verify this again, e.g.:

 

Note that this matches your specification as the slicer is changed. It's also only going to work for this particular scenario, so you'll need to apply these principles if you want to work with different fields within your model.

Hopefully this should be all you need. Good luck!

Daniel

---

If my post solves your challenge, then please consider accepting as a solution to help other forum members find the answer more quickly 🙂

[Alessandro-laba]

Hi @dm-p, 

Thank you very much that's exactly what I was looking for. Much appreciated.

Thanks, 

Ale