DAX help: % monthly share of another table

Question

I have a DAX formula for my Powerpivot I cannot get to solve and was hoping for help.

I have two pivot tables connected already

Showing a cohort of actions taken within Month 1,….X on the sign up month
Total Sign Ups on monthly basis

I have tried to attached the sheet here but somehow I cant so I have add a screenshot of the sheet.1

What I have so far is: =DIVIDE( SUM(Range[conversion to KYC completed]), SUM('Range 1'[Sum of signups]))

But this does not give me what I want as I think I’m missing the monthly grouping somehow.

Question 1: What I want is to get the share of actions completed within 1,...,X months out of the total sign up that given month (e.g. Jan) (so the data from Table 2)

Question 2: In best case I would also like to show total sign ups in the beginning of the cohort to make the cohort easier to understand, so having the monthly total sign up (which the cohort is calculated based on). But now I cannot get just the totals month by month. Is there anyways just to add in a monthly total column in the pivot without applying these number as a value across all columns?

Something like this is the ultimate outcome for me 2

UPDATED WITH SAMPLE DATA

Signup month, KYC completed month, Age by month, signups, conversion to KYC completed Jan-17 Jul-18 18 97 75 Jan-17 Jul-18 18 99 79 Jan-17 Dec-18 23 95 80 Feb-17 May-18 15 99 74 Feb-17 Jul-18 17 90 75 Feb-17 Jul-18 17 95 76 Feb-17 Aug-18 18 92 71 Mar-17 May-18 14 94 73 Apr-17 Jul-18 15 93 75 May-17 Sep-18 16 94 70 May-17 Oct-18 17 98 72 Jun-17 May-18 11 95 79 Jul-17 Oct-18 15 97 74 Jul-17 Jul-18 12 94 78 Aug-17 Sep-18 13 96 74 Sep-17 Nov-18 14 95 80 Sep-17 Oct-18 13 94 79

DESIRED OUTCOME The % for Month 1....X is calculated KYC Completed / Monthly Sign up

OUTPUT WITH THIS CODE =VAR SignUpMonth = IF(HASONEVALUE('Range 1'[Row Labels]), BLANK()) RETURN DIVIDE(CALCULATE(SUM([conversion to KYC completed])), CALCULATE(SUM('Range 1'[Sum of signups]), FILTER(ALL(Range), Range[Signup month (Month Index)] = SignUpMonth)))

[ enter image description here

can you post some sample data and maybe a shot o your desired results? — StelioK

Frostytheswimmer Frostytheswimmer · Accepted Answer · 2019-06-25T22:56:05

Thanks for the sample data Franzi. Still not too clear what you're asking for, but perhaps this will help a little.

Signed Up to Signed In Ratio = 
VAR SignUpMonth = SELECTEDVALUE(Table1[Signup month], BLANK())
RETURN 
    DIVIDE(CALCULATE(SUM([conversion to KYC completed])),
           CALCULATE(SUM(Table1[ signups]),
               FILTER(ALL(Table1), Table1[Signup month] = SignUpMonth)))

So. Let's break it down.

If I understand correct, you want to see the cross section of number of signins for a given month ( x axis ) signup combo ( y axis ) and divide that number by the total signups ( y axis ) per signup month.

number of signins for a given month ( x axis ) signup combo ( y axis ):

CALCULATE(SUM([conversion to KYC completed]))

TOTAL signups ( y axis ) per signup month

CALCULATE(SUM(Table1[ signups]),
    FILTER(ALL(Table1), Table1[Signup month] = SignUpMonth))

DAX help: % monthly share of another table

1 Answers