Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dividing a Measure into Multiple Categorizations

Hi all,   I'm struggling with this graph I'm trying to create, and I could really use some help.   This is what my data looks like (just the upper three tables: Products, Entries and Durations). ...
Need Help
  • OwenAuger's avatar
    OwenAuger
    6 years ago

    Anonymous  thanks for the clarification 🙂

     

    I would do it this way (PBIX link )

    • Create a disconnected table called Color which has a column Color, with values "X" and "Y"
    • Create this measure to use in the clustered column chart:

     

     

    Measure for Visual = 
SUMX ( 
    VALUES ( Products[ID] ),
    IF (
        [have an entry], // True if [have an entry] is nonzero
        VAR HasC = 
            CALCULATE ( [have a duration], Durations[Category] = "C" ) // True if [have a duration] is nonzero when Category = "C"
        RETURN
        SUMX ( 
            Color,
            SWITCH ( 
                Color[Color],
                "X", IF ( HasC, 0.4, 1 ),
                "Y", IF ( HasC, 0.6 )
            )
        )
    )
)​​

     

     

    • Create a column chart with Color[Color] as the Legend and [Measure for Visual] as the measure. Set the colors for X & Y appropriately.
    • Result looks something like this:


    Regards 

    Owen

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
6 years ago

Anonymous - quick question on the visualization itself.

Assuming you use a stacked column like the one you've shown, do you need the column to be split so that it has up to two components per Product (one color X and one color Y)?

 

And do you need Color X & Color Y to be grouped by product, so that you would end up with an alternating pattern if each Product had both colours?

 

Or would you be happy to simply have one block of Color X & one block of Color Y, of appropriate total sizes, but not broken down as such?

 

Regards,

Owen

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi OwenAuger,

 

That's a good one. I see that my sketch could be confusing in that sense, sorry about that.

 

I would actually be very happy to just have one block of Color X & one block Color Y of appropriate total sizes!

 

Thanks for paying attention. 🙂 

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    6 years ago

    Anonymous  thanks for the clarification 🙂

     

    I would do it this way (PBIX link )

    • Create a disconnected table called Color which has a column Color, with values "X" and "Y"
    • Create this measure to use in the clustered column chart:

     

     

    Measure for Visual = 
SUMX ( 
    VALUES ( Products[ID] ),
    IF (
        [have an entry], // True if [have an entry] is nonzero
        VAR HasC = 
            CALCULATE ( [have a duration], Durations[Category] = "C" ) // True if [have a duration] is nonzero when Category = "C"
        RETURN
        SUMX ( 
            Color,
            SWITCH ( 
                Color[Color],
                "X", IF ( HasC, 0.4, 1 ),
                "Y", IF ( HasC, 0.6 )
            )
        )
    )
)​​

     

     

    • Create a column chart with Color[Color] as the Legend and [Measure for Visual] as the measure. Set the colors for X & Y appropriately.
    • Result looks something like this:


    Regards 

    Owen

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      OwenAuger 

       

      Amazing, thank you! 🙏 

       

      This DAX programming language looks simpler than it really is. At least, that's how I feel about it.

       

      I'm going to try this out on my actual dataset. I know you answered my question correctly at this point, so please forgive me for letting the topic open for a moment longer while I do some testing. I might try to ask you a little follow-up question, if you're willing to take a look ofcourse.

       

      Will be back!

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        OwenAuger 

         

        It's working great, thanks again! 😀

         

        I ended up splitting the measure into two separate ones, one for 'X' and one for 'Y'. Like this:

         

        Measure for Visual X = 
SUMX ( 
    VALUES ( Products[ID] ),
    IF (
        [have an entry], // True if [have an entry] is nonzero
        VAR HasC = 
            CALCULATE ( [have a duration], Durations[Category] = "C" ) // True if [have a duration] is nonzero when Category = "C"
        RETURN
             IF ( HasC, 0.4, 1 )
            )
        )

        &

        Measure for Visual Y = 
SUMX ( 
    VALUES ( Products[ID] ),
    IF (
        [have an entry], // True if [have an entry] is nonzero
        VAR HasC = 
            CALCULATE ( [have a duration], Durations[Category] = "C" ) // True if [have a duration] is nonzero when Category = "C"
        RETURN
         IF ( HasC, 0.6 )
            )
        )

         

        Which eliminated the need for an extra 'Color' table.