Calculation fields for each quarter

[Anonymous]

I have a list of measures in a matrix form. I would like to get a calculation for each of the Quarters like below:

Can anyone help me how I can make this work please?

A1, A2, B1, B2, B3, C2, D2 and Total are all measures, and I have used "switch values to rows" toggle to bring them in rows.

What I would like is:

 Norm (%) is (A1, Q1)*100/(Q1,Total) == (23*100)/154

Any ideas on how I can achieve this?

[grantsamborn]

Hi @Anonymous 

The steps I followed were

In Power Query:

1) Select the 1st column (Category in my example) and "Unpivot other columns"

2) renamed "Attribute" column to "Qtr"

In DAX, write these 2 measures:

Value = SUM( 'DataTable'[Value] )

Norm (%) = 
VAR _numerator = [Value]
VAR _denominator =
    CALCULATE(
        [Value],
        ALLSELECTED( 'DataTable'[Category] )
    )
VAR _result = 
    DIVIDE(
        _numerator,
        _denominator 
    )
RETURN
    _result

pbix: https://1drv.ms/u/s!AnF6rI36HAVkhPI_LGKwnceg4tjTtw?e=tfa89b

Let me know if this helps.

[Anonymous]

Thanks for the reply, However, my measures A1, A2....etc are not values in a column. They are calculations for each year. They wont be visible in Power query.

[grantsamborn]

Hi @Anonymous 

That definitely changes things.  I must have skipped by the line where you mentioned A1, A2, etc were measures.

I was going to ask if there is any way you can produce a sample dataset but that would have to include measures as well so I'm not sure there is much I can do.