Forum Discussion

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

Super 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

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

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.