% of Parent Row Total Measure

[Anonymous]

Hi,

I need help please with this measure as its not returning correct % of parent row total rather its returning 100% for ALL values instead of sum of each value to equal 100%

%JobToAccepInterval by Row Total = DIVIDE (
    SUM('FreightForward'[JOBCOUNT]),
    CALCULATE ( SUM('FreightForward'[JOBCOUNT]), ALLSELECTED(FreightForward[JobToAccep Interval])))

[vanessafvg]

can you share your results?

also what have you selected on your slicers, please give some examples of whats going wrong and whtat you are expecting.

[amitchandak]

@Anonymous , try

%JobToAccepInterval by Row Total = DIVIDE (
SUM('FreightForward'[JOBCOUNT]),
CALCULATE ( SUM('FreightForward'[JOBCOUNT]), removefilters(FreightForward[JobToAccep Interval])))

or

%JobToAccepInterval by Row Total = DIVIDE (
SUM('FreightForward'[JOBCOUNT]),
CALCULATE ( SUM('FreightForward'[JOBCOUNT]), ALLSELECTED(FreightForward)))

[Anonymous]

Hi @amitchandak, @vanessafvg 

The first measure didn't work but the 2nd measure is close but not quite right i.e. as per screenshot below, the sum of each % should equal 100% for each month

%JobToAccepInterval by Row Total = DIVIDE (
    SUM('FreightForward'[JOBCOUNT]),
    CALCULATE ( SUM('FreightForward'[JOBCOUNT]), ALLSELECTED(FreightForward)))

[Fowmy]

@Anonymous 

Try this measure: Replace [HOURS CATEGORY] with the name of the legend category you have which shows hrs 0-2 and so on.

%JobToAccepInterval by Row Total = 
DIVIDE (
    SUM('FreightForward'[JOBCOUNT]),
    CALCULATE ( SUM('FreightForward'[JOBCOUNT]), 
     ALLSELECTED([HOURS CATEGORY]))
)

________________________

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

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

YouTube  LinkedIn

: 

[Anonymous]

Hi @Fowmy, unfortunately that didn't either work either as per screenshot.

%JobToAccepInterval by Row Total = 
DIVIDE (
    SUM('FreightForward'[JOBCOUNT]),
    CALCULATE ( SUM('FreightForward'[JOBCOUNT]), 
     ALLSELECTED(FreightForward[JobToAccep Interval]))
)

[Fowmy]

@Anonymous 

You may share your PBIX file with sample data to check if the model is set up correctly.

________________________

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

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

YouTube  LinkedIn

[Anonymous]

Here you go

[tex628 · Accepted solution]

Try this,

%JobToAccepInterval by Row Total = 
VAR Month = SELECTEDVALUE('Calendar'[MonthNum])
Return
DIVIDE(SUM(FreightForward[JOBCOUNT]) , CALCULATE(SUM(FreightForward[JOBCOUNT]) , ALLSELECTED(FreightForward) , 'Calendar'[MonthNum] = Month) , BLANK())

/ J 

[Fowmy]

@tex628

Can you help me understand why removing filters did not work?

ALLSELECTED(FreightForward[JobToAccep Interval])

________________________

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

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

YouTube Linkedin

[tex628]

The calendar table filters FreightForward through the visual legend. The Legend is considered a context filter and is therefore removed by the ALLSELECTED() statement. I'm going to be perfectly honest and say that I'm not exactly sure of the behaviour of ALLSELECTED, it's incredibly complicated.

In my mind the legend of the calendar table should be seperate and unaffected by the ALLSELECTED(), but instead the filter is actually removed. 

If you want to try and grasp just how messed up that syntax is I would advice you to read this: 
https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/ 

Br,
J

[Fowmy]

@tex628 

Thanks a lot!

Lemme start my experiments 🙂

Thanks again
Fowmy

[Anonymous]

@tex628that worked perfectly, thank you. Much appreciated:)

[tex628]

Your allselected statement is removing the filter on month since your month column is in the same table (or doublesided crossfilter) as the freightforward column. 

If the column is in the same table you should create a calendar dimension table. 
If you have a doublesided relationship you should change the crossfilter, either in the relationshipmodel or in the calculation using CROSSFILTER()

Br,
J

[Anonymous]

Hi @tex628, the date column is in a separate calendar table.

[tex628]

The crossfilter doublesided or one way? 

[Anonymous]

one way