Trouble with % of Total calculation

[jam17]

As a first step in a more complicated calculation, I am simply trying to get the % of subtotal for the number of survey launches, but I'm not being able to successfully ignore the necessarily filter.

In the image, I am returning the _all_launches_ variable, which I am expecting to how the Total of each each for Survey Launches, as the value for % Launches for each item in that year.  Example, for 2023, I would am expecting the % launches to currently return 6658 for each Use Case Broad item.

That way when I RETURN num_launches/all_launches in my measure, I will get the % of launches for each item for that year.  If I do ALL('Mockup Data') instead, I do get a flat number through, but it's the 19198, because it also ignores the year.

The idea in the  measure as well is so the user can apply slicers to this table, and see the data filtered to, say, a specific sales region.

What am I doing wrong?

[v-karpurapud]

Hi @jam17 

Thank you for reaching out to the Microsoft Fabric community forum.
 

The issue lies in how the denominator is being calculated. Currently, your ALL('Mockup Data') function removes all filters, including the Year filter, which results in a value of 19,198 instead of the correct subtotal for the year. To calculate the Year subtotal while ignoring only the row-level Use Case Broad filter, you can use:

 

% Launches =

VAR Num =

    [Survey Launches]



VAR YearTotal =

    CALCULATE(

        [Survey Launches],

        REMOVEFILTERS('Mockup Data'[Use Case Broad])

    )

RETURN

    DIVIDE(Num, YearTotal)

This removes the Use Case Broad filter but keeps the Year and any slicers the user applies, giving you the correct subtotal per year.

 

I hope this information is helpful. If you have any further questions, please let us know. we can assist you further.

Regards,

Microsoft Fabric Community Support Team.

[jam17]

Except I'm not using ALL.  If you look at the image I provided, my all_launches formula is the same as your YearTotal formula, but it's still splitting it by Use Case Broad

[Ashish_Mathur]

Hi,

Does it work if you replace REMOVEFILTERS() with ALL()?  Also, consider using visual calculations.

[Greg_Deckler]

@jam17 Not sure you are doing anything wrong but difficult to tell without your formula for Survey Launches. 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[jam17]

The Survey Launches is a CALCULATE(DISINCTCOUNTNOBLANKS(xxx)) of survey IDs to determine the number of surveys.

I've tried it with that directly in the forumula, as an external measure (as here), with and without calculate.  This formula pulls up the correct numbers, as i've compared it to just dropping in COUNT(DISTINCT) aggregation of survey id.