Measure Output is Reversed in Stacked Column Chart

[hunterc]

Hi, 

I have a measure that I put into a stacked column chart, but the data are flipped. In the screenshot, January should be PM Complete? 9.63% = true and 90.37% false, but as you can see, it's reversed.

Is my measure the issue? The measure is essentially taking a % of total if certain conditions are met (month#=1 and Complete?=true). 

if(values('Dates'[Month #])="01",

CALCULATE(COUNT('WorkOrders'[Complete?]),

'Dates'[Month #]="01",

'WorkOrders'[Comlete?]="true")/[TotalWorkOrders],FALSE())

I don't think the measure is the issue, I've broken down the measure into parts and tested it on a card visual to make sure the numbers being produced are what they should be, and they are. There seems to be a miss when I throw the measure into the graph.

In addition - I'm not sure why in the auto generated tooltip from the screen shot, next to the %, there's a zero. There are actually thousands that met the criteria in the measure. This is somewhat secondary to the question posted above, but still an issue none the less.

Any help greatly appreciated, thanks!!

[v-rzhou-msft]

Hi @hunterc 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft]

Hi @hunterc 

I think you want to calculate the percent in this logic:

If month = 1, false/true per total(1 month) divide total 1 month.

If month = 2, false/true per total(1&2 month) divide total 1&2 month.

And show the result in 100% stacked column chart.

I build a WorkOrders and a DimDate table to have a test.

WorkOrders Table:

DimDate Table:

DimDate = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 01, 01 ), DATE ( 2020, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month #", MONTH ( [Date] ),
    "Fulldate Month", FORMAT([Date],"MMMM"),
    "Day", DAY ( [Date] ),
    "Datekey",
        FORMAT ( [Date], "yyyy" ) & ""
            & FORMAT ( [Date], "mm" ) & ""
            & FORMAT ( [Date], "DD" )
)

Result:

Build relationships between the Datekey columns in two tables.

Then build a measure to achieve your goal.

Measure = 
VAR _PerC =
    CALCULATE (
        COUNTROWS ( WorkOrders ),
        FILTER (
            ALLEXCEPT (
                WorkOrders,
                WorkOrders[u_asset_type_display_value],
                WorkOrders[Complete?]
            ),
            WorkOrders[Month #] <= MAX ( WorkOrders[Month #] )
        )
    )
VAR _Total =
    CALCULATE (
        COUNT ( 'WorkOrders'[u_asset_type_display_value] ),
        FILTER (
            ALLEXCEPT ( WorkOrders, WorkOrders[u_asset_type_display_value] ),
            WorkOrders[Month #] <= MAX ( WorkOrders[Month #] )
        )
    )
RETURN
    DIVIDE ( _PerC, _Total )

We need to sort the Fulldate Month column by Month # column in DimDate Table, and then build the visual.

Result:

We can use Slicer to choose which type to show.

You can download the pbix file from this link: Measure Output is Reversed in Stacked Column Chart

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[hunterc]

@v-rzhou-msft thanks! This works perfectly for q1 data, but seems to not work after that, I'm guessing because of the <=max [month #] portio. For example in month 5, wouldn't it be pulling months 1 through 5 in your measure? Due to quarterly reporting,  month 5 would need to only be taking into account months 4 and 5, month 12 would need to take into account 10, 11 and 12 (since it's a different quarter). Same thing for q3, q4, etc. Any thoughts on how to incorprate that into the measure as well?

Thanks,

Hunter

[v-rzhou-msft · Accepted solution]

Hi @hunterc 

I update the Fact Table, Dimdate table and the measure.

Dimdate Table:

DimDate = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 01, 01 ), DATE ( 2020, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month #", MONTH ( [Date] ),
    "Quarter", QUARTER([Date]),
    "Fulldate Month", FORMAT([Date],"MMMM"),
    "Day", DAY ( [Date] ),
    "Datekey",
        FORMAT ( [Date], "yyyy" ) & ""
            & FORMAT ( [Date], "mm" ) & ""
            & FORMAT ( [Date], "DD" )
)

Add a Qtr # column in Fact Table.

Qtr # = RELATED(DimDate[Quarter])

Update Measure:

Measure = 
VAR _PerC =
    CALCULATE (
        COUNTROWS ( WorkOrders ),
        FILTER (
            ALLEXCEPT (
                WorkOrders,
                WorkOrders[u_asset_type_display_value],
                WorkOrders[Complete?]
            ),
            WorkOrders[Month #] <= MAX ( WorkOrders[Month #] )
                && WorkOrders[Qtr #] = MAX ( WorkOrders[Qtr #] )
        )
    )
VAR _Total =
    CALCULATE (
        COUNTROWS ( 'WorkOrders' ),
        FILTER (
            ALLEXCEPT ( WorkOrders, WorkOrders[u_asset_type_display_value] ),
            WorkOrders[Month #] <= MAX ( WorkOrders[Month #] )
                && WorkOrders[Qtr #] = MAX ( WorkOrders[Qtr #] )
        )
    )
RETURN
    DIVIDE ( _PerC, _Total )

Result is as below.

Default:

Select type in Slicer:

You can download the pbix file from this link: Measure Output is Reversed in Stacked Column Chart

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[hunterc]

@v-rzhou-msft works great, thanks very much again for your help!

[Greg_Deckler]

@hunterc - OK, I have attempted to replicate this. See my PBIX file attached below sig. You want Table (37).

[hunterc]

@Greg_Deckler  Greg, thanks agin for your help. This is probably my fault by trying to dumn down the sample data as much as possible...but I will need to create a measure (although with the sample data I provided, throwing it into a graph is the most logical thing to do). I need a measure because I'm trying to get a cumulative qtd completion %. For example, if month =1, take the % of completed work orders in month 1. If month = 2, then I need to sum the amount of completed work orders from month 1 and month 2, dividied by the total amount of workorders from those 2 months and so on for each quarter. This is why in the original post there was an "if" measure to say if month = "x" then do "y" as I want it to act differently depending on which month it is. There are also other options besides "preventative maintenance", so there were some filters in the if statement as well. Please let me know if that makes sense. Thanks again for your continued help!

[Greg_Deckler]

@hunterc If I understand correctly, in general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[amitchandak]

@hunterc , Can you share sample data and sample output in table format?

Please try following

if(max('Dates'[Month #])="01",divide(
CALCULATE(COUNT('WorkOrders'[Complete?]),
'Dates'[Month #]="01",
'WorkOrders'[Comlete?]="true"),[TotalWorkOrders]),FALSE())

or

if(max('Dates'[Month #])="01",divide(
CALCULATE(COUNT('WorkOrders'[Complete?]),
'Dates'[Month #]="01",
filter('WorkOrders','WorkOrders'[Comlete?]="true")),[TotalWorkOrders]),FALSE())

[hunterc]

@Greg_Deckler @amitchandak - Thanks very much for your willingness to take a look. 

I have added sample data below, 1 TRUE, 3 FALSE in Jan

The measure denominator: 

TOTALWOs=(count('WorkOrders'[u_asset_type_display_value]),'DimDate'[fulldate],'WorkOrders'[u_asset_type_display_value]="Preventative Maintenance")

FYI - 'DimDate' is just a date table, this is then filtered at the visual level depending on what's being shown (Connection betwen 'DimDate' and 'WorkOrders' is made through [fulldate]). Also, the numerator is filtered by [u_asset_type_display_value] for "Preventative Maintenance" at the visual level

u_asset_type_display_value	Complete?	Datekey	Month #
Preventative Maintenance	TRUE	20200121	01
Preventative Maintenance	FALSE	20200120	01
Preventative Maintenance	FALSE	20200117	01
Preventative Maintenance	FALSE	20200116	01

The result from the sample data should be 25%% TRUE, 75% FALSE. From the graphic I showed previously, the FALSE flag would have been applied to the 75% in this sample data case. 

Thanks again!

[Greg_Deckler]

@hunterc - Hard to say really, I don't see the formula for the PM Complete calculation? 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.