Custom Calculation - hierarchy

DAX DAX Calculations
1

Hello everyone, i have this need to create a custom measure based on the hierarchy of the node displayed in the matrix and could use some help with a dax.

I have a matrix table, and it has category, item data with Current Fiscal Year (CFY) - Sales, Units, Prior Fiscal Year (PFY) Sales, Units and a Weighted Revenue Measure - derived as ‘price from last year - PFY sales/ PFY quantity’ * CFY quantity… as shown in the image below…

image
image739×180 12.6 KB

So, the ask is to sum all the item Weighted Revenue when this measure is displayed for the ‘Category’ row, where as at an item level, it needs to perform the above listed calculation…

I tried using ‘isfiltered’/hasonefilter, isinscope’ functions to identify the row - find if row displayed is a category row or an item row, to then find a way to apply the appropriate rules but i am not getting the desired results…

thanks for reading my post, appreciate any suggestions!

regards

2

@sanappi09 PFB solution for your requirement

Updated the dax query to work for multiple categories.

Category
Category991×584 41.9 KB

Category.pbix (42.0 KB)

1 Like
3

Hi @sanappi09.

Just add a HASONEVALUE check to the weighted revenue calculation, use your normal formula for the detail rows, and use a SUMX for the total row.

Weighted Revenue CORRECT TOTAL =
IF(
    HASONEVALUE( Items[CY Revenue] ),
    // calculate the weighted revenue for the DETAIL rows
    IF(
        [FY19 Units] = 0,
        [FY20 Revenue],
        [FY20 Units] * DIVIDE( [FY19 Revenue], [FY19 Units], 0 )
    ),
    // calculate the weighted revenue for the TOTAL row
    SUMX(
        Items,
        [FY20 Units] * DIVIDE( [FY19 Revenue], [FY19 Units], 0 )
    )
)

There are many resources online dealing with incorrect “simple” DAX totals, including this eDNA YouTube video:

Hope this helps.
Greg

TEST Items.pbix (20.9 KB)
TEST Items.xlsx (9.8 KB)

1 Like
4

thanks Greg, i had tried to use ‘hasonevalue’ but wasn’t producing desired results, found that i was using a wrong item table reference…

5

thanks Rajesh!

6

You’re Welcome