DAX: Divide by sum of multiple options

[david_MAS]

Hi,

I'm trying to write a calculate function comvbined with divide that should output a percentage.

Background: I have a measure which is grouped by a field called "Channel" which has 8 categories within it.

What I want is to get the proportion of the measure where the Channel used is the combined total of 3 specified channels but i'm getting an error (copied below the code).

% actual  Delivery = 
CALCULATE(
    DIVIDE(
        [Volumes (by Month)],
        ([Volumes (by Month)]
        ('1819 transposed'[Channel ] && "F2F" && "Online" && "Tel"))
                ))

See code above - think there's something obvious I'm missing but any help greatly appreciated:

Error Message:
MdxScript(Model) (98, 10) Calculation error in measure '1819 transposed'[% actual Delivery]: Cannot convert value 'Pending' of type Text to type True/False.

[v-yulgu-msft]

Hi @david_MAS ,

Please provide sample data and desired output.

How to Get Your Question Answered Quickly

Regards,

Yuliana Gu

[david_MAS]

Hi,

Okay, so sample data below - essentialy what I want is to get the sum of the first 3 channels below, but as a percentage of the entire value of the column "volume"

Channel	Volume
F2f	10
Online	2
Tel	4
No shows	6
Pending	8
Referred online	9
Reffered tel	1
Self help	0

Hope that makes sense and thank you for the reply.

[v-yulgu-msft · Accepted solution]

Hi @david_MAS ,

Please refer to below measures:

Sum certain channel =
CALCULATE (
    SUM ( '1819 transposed'[Volume] ),
    FILTER (
        ALLSELECTED ( '1819 transposed' ),
        '1819 transposed'[Channel] IN { "F2f", "Tel", "Online" }
    )
)

Percentage =
DIVIDE (
    [Sum certain channel],
    CALCULATE ( SUM ( '1819 transposed'[Volume] ), ALL ( '1819 transposed' ) )
)

Best regards,

Yuliana Gu

[david_MAS]

Hi Yuliana,

Thanks for this, that's looking realy good!

As an extension to this, say I wanted to show this percentage month-by-month (not cumulative) based on the UK-standard financial year, would this involve anothr filter within this measure or is it more striagtforward than that?

Thanks

[v-yulgu-msft]

Hi @david_MAS ,

With current sample data, I cannot see any relative date field to support Month-by-Month calculation. Since the original question has been worked out, would you please kindly mark the helpful reply as an answer? For further requirement, I would suggest you post a new thread on forum with more detailed sample data together with desired output to make the scenario more clear, so that more community members can view it and provide advice.

Best regards,

Yuliana Gu