Split category value based on % calculation rule:

Power BI Data Model
1

Refer to attached .pbix file and supporting table data source. I believe the problem is clearly stated enough :wink: . Perhaps some type of somewhat complicated Switch statement(s) is required…

image
image924×289 7.2 KB

split category into sub-category.pbix (36.3 KB)

Split category into sub-category.xlsx (12.5 KB)

2

Hello @mdalton2100,

Thank You for posting your query onto the Forum.

Firstly, I created a table and added a “Sub-Category” column against the “Category” criteria. Below is the screenshot of the newly created table and the status of the relationship created between the source table and newly created table is provided for the reference -

Category and Sub-Category Table

Relationship Status
Relationship Status832×550 15.4 KB

Now, once the table is created and the relationship is established. I created two measures, one to calculate the results based on condition that you’ve specified and other one created just to fix the grand totals. Below are the measures provided for the reference -

Conditional Amount = 
SWITCH( TRUE() ,
    SELECTEDVALUE( 'Category and Sub-Category'[Category] ) = "X" && 
    SELECTEDVALUE( 'Category and Sub-Category'[Sub-Category] ) = "A" , 
        DIVIDE(
            CALCULATE( SUM( 'Source Fact table'[Amt.] ) , 
                'Source Fact table'[Category (v1)] = "X" ) , 
            1.25 , 
            0 ) * 25 / 100 ,

    SELECTEDVALUE( 'Category and Sub-Category'[Category] ) = "X" && 
    SELECTEDVALUE( 'Category and Sub-Category'[Sub-Category] ) = "B" , 
        DIVIDE(
            CALCULATE( SUM( 'Source Fact table'[Amt.] ) , 
                'Source Fact table'[Category (v1)] = "X" ) , 
            1.25 , 
            0 ) ,

    SELECTEDVALUE( 'Category and Sub-Category'[Category] ) = "Y" &&
    SELECTEDVALUE( 'Category and Sub-Category'[Sub-Category] ) = "Y" , 
        CALCULATE( SUM( 'Source Fact table'[Amt.] ) , 
            'Source Fact table'[Category (v1)] = "Y" ) , 

    SELECTEDVALUE( 'Category and Sub-Category'[Category] ) = "Z" &&
    SELECTEDVALUE( 'Category and Sub-Category'[Sub-Category] ) = "Z" , 
        CALCULATE( SUM( 'Source Fact table'[Amt.] ) , 
            'Source Fact table'[Category (v1)] = "Z" ) ,
            
BLANK() )



Conditional Amount - Totals = 
SUMX(
    SUMMARIZE(
        'Category and Sub-Category' , 
        'Category and Sub-Category'[Category] , 
        'Category and Sub-Category'[Sub-Category] , 
        "@Totals" , 
        [Conditional Amount] ) , 
    [@Totals]
)

Once these two measures are created, you’ll be able to see the results in the table based on the condition that you’ve specified. Below is the screenshot of the final results provided for the reference -

Final Results
Final Results1406×765 93.1 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

split category into sub-category - Harsh.pbix (42.9 KB)

2 Likes
3

Works beautifully, Mr. Harsh. Thanks for the lesson!

Regards,
Marcus

4

Hello @mdalton2100,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to assist you.

This was a unique query where we had to first insert the sub-category and than calculate the proportions accordingly.

Thanks and Warm Regards,
Harsh

5

@Harsh,

Really elegant solution and great explanation. Enjoyed working through that one…:clap: :clap:

  • Brian
1 Like
6

One other, related question. I’d like to combine the calculated result for the “X” and “A” combination with another measure. I tried, but it seems that the current context blocks that measure from calculating … was hoping that ‘Allexcept’ might do it, but unsuccessful. I could enhance the .pbix to illustrate this issue:

image

7

Hello @mdalton2100,

Can I request you one small thing? Can you please create a new thread for the related new question?

Since as per the forum guidelines one question can be posted under one thread.

Also post a PBIX file alongwith the scenario that you’re trying to achieve and we’ll look into it. :+1:

Thanks and Warm Regards,
Harsh

8

Sure, will do – thanks again.

Signing off (for now),
Marcus