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  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.