Support Required to distribute the values based on other table and then sum up

[Anonymous]

Hi All,

I am new to DAX and needed your expertise to solve one of the issues I am stuck in. I need to distribute the based on their consumption and new releases has to be divided for blank values... I am attaching the sample ,how it looks like.

In the above my recharges tables has 7 denominations, so for that particular period i need to take the proportion for only B,C,D,E,F denominations,i above ex, it comes to be 0.1,0.15,0.2,0.25,0.3 respectively. With that proportion, I have to divide the ABC values in the releases table. Releases table also has B,C,D,E,F denominations. After dividing the values of ABC in B,C,D,E,F proportions we need to add with the values exists in the releases table. Hence the final figure which I am expecting for B,C,D,E,F denominations are 13500,20100,26700,33000 and 39900 respectively. 

Any help would be much appreciated.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Does my reply solve your problem?

If not, please let me know.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @Anonymous

1. create columns in two tables

link column1 in recharges tables, link column2 in releases table

link column1 =
CONCATENATE ( 'recharges tables'[denominations], 'recharges tables'[weekyear] )

link column2 =
CONCATENATE ( 'releases table'[DENO], 'releases table'[weekyear] )

2. 

create relationship between two tables based on link columns

3.

create a column in releases table

abc =
CALCULATE (
    MAX ( 'releases table'[VALUE] ),
    ALLEXCEPT ( 'releases table', 'releases table'[weekyear] )
)

4. create measures

Measure =
SUM ( 'recharges tables'[values] )
    / CALCULATE (
        SUM ( 'recharges tables'[values] ),
        FILTER (
            ALLSELECTED ( 'recharges tables' ),
            'recharges tables'[weekyear] = MAX ( 'recharges tables'[weekyear] )
        )
    )


Measure 2 = [Measure]*MAX('releases table'[abc])

Measure 3 = [Measure 2]+MAX('releases table'[VALUE])

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.