Measure required in Power BI matrix

[PaisleyPrince]

Hi,

I have a matrix showing values by weeks and months but i only want the week % to be shown as that related to the current month rather than the overall total as per the screenshot. Can you please advise how this would be done ?

[PaisleyPrince]

Hi,

I have a matrix showing values by weeks and months invoice values by division but i want the division & week % to be shown as that related to the current month total rather than the overall total as per the screenshot. Can you please advise how this would be done ?

[krishnakanth240 · Accepted solution]

Hi @PaisleyPrince

Please check and confirm

Base measure
Total Invoice Value :=
SUM ( FactInvoices[InvoiceValue] )

Final single % measure (use this in matrix)
Div Week % of Month :=
VAR MonthTotal =
CALCULATE (
[Total Invoice Value],
REMOVEFILTERS ( Date[WeekNo], FactInvoices[Division] )
)
RETURN
DIVIDE ( [Total Invoice Value], MonthTotal, 0 )

[cengizhanarslan]

Please check the formula below:

Div1 % =
DIVIDE(
    [Inv Div1],
    CALCULATE( [Inv Div1], REMOVEFILTERS('Date'[WeekNo]) )
)

and use it for 2 and 3 by creating seperate measures. 

[Tahreem24]

@PaisleyPrince Try this measure:

Measure =DIVIDE( [Invoice Value], CALCULATE([Invoice Value],REMOVEFILTER('Date'[Week No])))

[rohit1991]

Hii @PaisleyPrince 

You need a measure that calculates the % against the current month total, not the grand total. Use CALCULATE with ALLEXCEPT (or REMOVEFILTERS) to keep the Month context while removing Week/Division filters. For example:

Div Week % =
DIVIDE(
    [Invoice Value],
    CALCULATE(
        [Invoice Value],
        ALLEXCEPT('Date', 'Date'[Month])
    )
)

[v-aatheeque]

Hi @PaisleyPrince 

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.

[v-aatheeque]

Hi @PaisleyPrince 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

[Praful_Potphode]

Hi @PaisleyPrince ,

Please try below visual calculation(select your Matrix visual and click new visual calculation) in your matrix:

Percent of parent = FORMAT(
DIVIDE(
    [Sum of Volume]
,
COLLAPSE([Sum of Volume],ROWS,1)

),"0.00%")

Sample PBIX

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[krishnakanth240]

Hi @PaisleyPrince 

Please check and confirm with headsup. Thank You!

Week % of Month =
VAR WeekVolume =
SUM ( Fact[volume] )

VAR MonthVolume =
CALCULATE (
SUM ( Fact[volume] ),
REMOVEFILTERS ( 'Date'[Week No] )
)

RETURN
DIVIDE ( WeekVolume, MonthVolume )

[Ashish_Mathur]

Hi,

Try this measure

Total of month = calculate([Volume],allexcept(calendar,calendar[month]))

Week % = divide([Volume],[Total of month])

I have assumed that volume is an explicit measure.

[Zanqueta]

Hi @PaisleyPrince,

Assuming you already have a measure for Sales (or similar), you can create a new measure for Week % of Month:

Week % of Month =
DIVIDE(
    [Total Sales],  // numerator: sales for the current week
    CALCULATE(
        [Total Sales],
        REMOVEFILTERS('Date'[Week]),       // remove week filter
        KEEPFILTERS(VALUES('Date'[Month])) // keep current month context
    ),
    0
)

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

[cengizhanarslan]

Please check the formula below:

Week %  =
DIVIDE(
    [Volume],
    CALCULATE(
        [Volume],
        REMOVEFILTERS('Date'[WeekNo])
    )
)

[Cookistador]

hI @PaisleyPrince 

The following measure should return what you want:

This is the measure I created:

Week % of Month =
VAR CurrentVolume = SUM('Table'[Volume])

VAR MonthTotal =
CALCULATE(
SUM('Table'[Volume]),
REMOVEFILTERS('Table'[Week No])
)

RETURN
DIVIDE(CurrentVolume, MonthTotal)