Data Bars Shading Question

Power BI Data Model
1

Hi Fellow Power BI Users -

I’ve been following your tutorial ‘Data Bars in Table Visualizations - Significant Viz Update in Power BI’ and had a question.

I have an issue where if a particular month is selected in a filter, the data bar shading does not shade the full bar even if the achieved% is 100% or more Screen shot below)

image

image

The DAX measure:

    % Achieved = 
    VAR investpcent =
        DIVIDE ( [Adviser Selected Lodged FUM (All)], [All Adviser Lodged FUM Target], 0 )
    VAR riskpcent =
        DIVIDE ( [Adviser Lodged Premium (All)], [All Adviser Lodged Premium Target], 0 )
    VAR totalvalue = riskpcent + investpcent
    VAR investreturnvalue =
        IF ( investpcent > 0, investpcent, "" )
    VAR riskreturnval =
        IF ( riskpcent > 0, riskpcent, "" )
    VAR totalreturnval =
        IF ( totalvalue > 0, totalvalue, "" )
    RETURN
        SWITCH (
            TRUE (),
            [New Business Lodgement Type] = "Investment", investreturnvalue,
            [New Business Lodgement Type] = "Risk", riskreturnval,
            totalreturnval
        )

Adviser Selected Lodged FUM (All) = 
CALCULATE (
    [All Adviser Lodged FUM],
    FILTER ( 'Access DB Lodgements', 'Access DB Lodgements'[Excluded FUM] = "No" )
)

Adviser Lodged Premium (All) = 
CALCULATE (
    SUM ( 'Access DB Lodgements'[Annual Premium/FUM] ),
    FILTER (
        'Access Active Advisers',
        'Access Active Advisers'[Access Level] <> ""
    ),
    FILTER (
        'Access DB Lodgements',
        'Access DB Lodgements'[Type] = "Risk"
    )
)

All Adviser Lodged FUM Target = 
CALCULATE (
    SUM ( 'Access DB Targets'[trgtTargetValue] ),
    FILTER (
        'Access Active Advisers',
        'Access Active Advisers'[Access Level] <> ""
    ),
    FILTER (
        'Access DB Targets',
        'Access DB Targets'[trgDescrip] = "Lodgement"
            && 'Access DB Targets'[trgType] = "FUM"
    )
)

All Adviser Lodged Premium Target = 
CALCULATE (
    SUM ( 'Access DB Targets'[trgtTargetValue] ),
    FILTER (
        'Access Active Advisers',
        'Access Active Advisers'[Access Level] <> ""
    ),
    FILTER (
        'Access DB Targets',
        'Access DB Targets'[trgDescrip] = "Lodgement"
            && 'Access DB Targets'[trgType] = "Premium"
    )
)

Thanks in advance

2

Confusing this one.

All I can think of is that there is a higher value somewhere. So the one you are showing in this post is not the highest values from all the other values that are calculating for that column in your table.

That’s really all I believe it could be.

3

Hi Sam,

Thanks for the response.

In an effort to find out the issue i did it for just 1 month and can see that for the value 53.09% it is showing the bar is completely shaded.

How do i go about correcting the issue on an individual basis? ie- for each person

image

Adviser Lodged Premium (All) = 
CALCULATE (
    SUM ( 'Access DB Lodgements'[Annual Premium/FUM] ),

    FILTER (
        'Access DB Lodgements',
        'Access DB Lodgements'[Type] = "Risk"
    )
)

All Adviser Lodged Premium Target = 
CALCULATE (
    SUM ( 'Access DB Targets'[trgtTargetValue] ),
    FILTER (
        'Access Active Advisers',
        'Access Active Advisers'[Access Level] <> ""
    ),
    FILTER (
        'Access DB Targets',
        'Access DB Targets'[trgDescrip] = "Lodgement"
            && 'Access DB Targets'[trgType] = "Premium"
    )
)

Test% = divide([Adviser Lodged Premium (All)],'Adviser FY Targets (All)'[All Adviser Lodged Premium Target],0)

Thanks

4

Ah, I think I know why.

Data bars, especially with percentages are always going to look somewhat odd, because they will always attempt to distribute based on the high and low numbers in any particular column from a table.

In you example 53.09% is the highest number in the current context in the page so it has the highest data bar. Then 2.93% (the first result) has a bar the is related proportionately to the rest of the results.

So to me it’s actually working as it should. I’m not sure you can change it.

I hope you understand what I mean here.

Sam

5

Hi Sam,

Thank you for the feedback. Yes I understand what you mean.

Are you aware or have any suggestions from a visual perspective on how i can showcase/represent progress completed? (Achieved vs Target)

Doesn’t necessarily have to be a databar. It could also be a progress bar?- If there isn’t an alternative then I will just showcase the % achieved values.

Thanks

6

I always use the coloured backgrounds for this like that.

The table visual is still the way to go in my view, just try the coloured background formatting.