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

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.