DAX to manually calculate percentage

[tokhir]

I have following DAX:

Measure =

IF (

    ISINSCOPE ( 'Cводная по оплатам'[Subcategory] ),

    SUM ( 'Cводная по оплатам'[Разница] ),

    IF ( ISINSCOPE ( 'Cводная по оплатам'[Категория] ), BLANK(), BLANK() )

)

and folloing structure:

and the matrix below:

I have categories, its subcategories, sum by every month and percentage rate compared with previous month. Percentage rate i did in excel since i cant do this with DAX. I put rate as a second column in every month to see the difference in sum. So, my problem is as i use category overall Power Bi sum all percentages of every month, its wrong. Using the DAX which i provided above i can remove the percentage summing in overall. Now i want instead of blanks set the same logic as a subcategories, i mean calcualate percentage difference with overall categories sum
If its possible to calculate percentage rate for subcategories directly in DAX its also will nice

[dharmendars007 · Accepted solution]

Hello @tokhir , 

1. You can modify the DAX measure that calculates the percentage rate at the subcategory level and prevents aggregation at the category level

Percentage Rate =
IF(
ISINSCOPE('Сводная по оплатам'[Subcategory]),
DIVIDE(
SUM('Сводная по оплатам'[Разница]),
CALCULATE(SUM('Сводная по оплатам'[Разница]), ALL('Сводная по оплатам'[Subcategory]))),BLANK())

2. You can use the following DAX to display the sum for categories and the percentage rate for subcategories in one measure

Measure =
IF(
ISINSCOPE('Сводная по оплатам'[Subcategory]),
[Percentage Rate],
IF(
ISINSCOPE('Сводная по оплатам'[Категория]),
SUM('Сводная по оплатам'[Разница]),BLANK()))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN