Calculations between "categories"

[Ncio]

Hello,

I'm trying to calculate the ratio on each line based on a sum from another line.

Example, the ratio for requests that have the status "Done" is the sum for the "new request" divided by the sum for the "Done" request.

My issue is that I don't know how to get the sum for the "new request" on the other lines. If I try the measure:

Ratio =
    CALCULATE(
        SUM('Requests'[Number]),
        FILTER('Requests', 'Requests'[Status] = "New request"))
)

==> it's calculated only for the line "New request" and the result of this measure is not available on other lines, I don't get a value.
I guess I should do something with the "ALL" / "ALLEXCEPT" function but I couldn't find a solution.

Note: I'm not trying the achieve that exact visual, it's to show the expected data & calculations

Thank you for your help,

[Ncio]

Without the "sort by column" feature:

With the sort by column (it's the only change):

==> the ratio is not calculated anymore (column "test" on this screenshot)

[Ncio]

Hi,

I finally found the cause, but not the solution:

1. your first solution works on my main report as long as I don't use the "sort by column" feature.
2. If I use the "sort by column" feature, it doesn't calculate the measure for each row.

On google, I could find some "side effect" of "sort by column", but I couldn't find how to solve it for your first answer:

Ratio =
 DIVIDE(
    SUM('Requests'[Number]),
    CALCULATE(
        SUM('Requests'[Number]),
        'Requests'[Status] = "New request"
    )
)

==> Any idea how to solve it?

==> I tried to upload the pbix here, but I get an error message.

[Ncio]

It does, thanks!

(I still don't understand why your first solution worked well on the dummy data, but not the real one, but I'm happy that it works 🙂   )

[Fowmy · Accepted solution]

@Ncio 

Can you try this:?, If it doesn't work, please share our dummy PBIX file to check.

Ratio = 
DIVIDE(
    SUM('Suivi des formations'[Valeur]),
    CALCULATE(
        SUM('Suivi des formations'[Valeur]),
        filter(all('Suivi des formations'),
          'Suivi des formations'[Type (détaillé)] = "Demandes reçues"
        )
    )
)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Ncio]

The measure:

Ratio = 
DIVIDE(
    SUM('Suivi des formations'[Valeur]),
    CALCULATE(
        SUM('Suivi des formations'[Valeur]),
        'Suivi des formations'[Type (détaillé)] = "Demandes reçues"
    )
)

The source data (partial values):

[Fowmy]

@Ncio 

Can you share the screenshot of your visual? 

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Ncio]

As I cannot share my "real" dashboard, i've created a dummy one... and your solution works perfectly... \o/

==> But it doesn't solve my issue on my real dashboard, the ratio stays empty :o(

* The tables between the dummy example and the real ones are very similar (some more columns, that's all)
* I've inactivated all links & filters, and it doesn't change anything
I'm not sure what could go wrong..

[Ncio]

Hello,

Thank you for your answer, however it doesn't work:

• On the line "New request" it calculates 100% (makes sense : 45/45 = 100%)
• On the other lines, it's not calculated at all: for instance, the ratio on the line "Done" is empty (same for planned & cancelled)

[Fowmy]

@Ncio 

Please try below measure for the ratio:

Ratio =
 DIVIDE(
    SUM('Requests'[Number]),
    CALCULATE(
        SUM('Requests'[Number]),
        'Requests'[Status] = "New request"
    )
)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn