Forum Discussion

hunterc's avatar
hunterc
Frequent Visitor
6 years ago
Solved

Measure Output is Reversed in Stacked Column Chart

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 reve...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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. 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
hunterc
Frequent Visitor
6 years ago

Anonymous 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      hunterc
      Frequent Visitor
      6 years ago

      Anonymous works great, thanks very much again for your help!