% of the subtotal

[logistics_power]

Hi experts,

I have a matrix visual as below with values in M3.

I would like to get a subtotal% split for each supplier region name.

Output measure expected as per column "M3% of Subtotal"

can anybody help with a DAX?

PS: Data is from datawarehouse model in which each of these fields are from different cubes like Supplier, Destination, Transaction type and Sales (M3).

[raji_n]

@logistics_power  Try this please,

Measure = SUM('Table'[Value])/CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Region], [destination title] ,[transaction type]))

[logistics_power]

@raji_n 

the columns destination title and transaction type are from different tables linked within the model.

May be thats why dax formula is not accepting when I add the 2 columns. Supplier region name is from table Supplier.

any work around for such scenario?

thanks

[raji_n · Accepted solution]

https://community.fabric.microsoft.com/t5/Desktop/Multiple-AllEXCEPT/td-p/2451860

please check this link, 

# Of Job Order ID's (Specific Purpose) = 
if(
    max('Job Stage (with Total Vacancies)'[Value])="Total Vacancies",    
        CALCULATE(
            [# Of Job Order ID's],                                   
            ALLEXCEPT(                
                'FACT_JobCandidate-Current_LastWeek_Combined',
                'FACT_JobCandidate-Current_LastWeek_Combined'[Job Stage],
                'FACT_JobCandidate-Current_LastWeek_Combined'[Source Period]               
            ),
            ALLEXCEPT(
                'Cost Centre',
                'Cost Centre'[Cost Centre]
            )
        ),                
    CALCULATE(
        [# Of Job Order ID's],
        USERELATIONSHIP('Job Stage (with Total Vacancies)'[Value],'FACT_JobCandidate-Current_LastWeek_Combined'[Job Stage])
    )
)

[raji_n]

@logistics_power  do you have a sample dump or pbix that you can share for me to review it?

In the allexcept part you have to add other 2 columns as well, like in your case destination title and transaction type.

[logistics_power]

@raji_n 

Unfortunately not. I dont have sample pbix.

can you tell me how to add the other 2 columns. I ll try that solution.

thanks

[raji_n]

Measure = SUM('Table'[Value])/CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Region]))

@logistics_power 

 Please mark this as a solution, if it solves your problem.

[logistics_power]

Hi Raji_n

for some reason I am seeing 100% on each row.

I tested the part CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Region])) and it is giving me the same result as the value M3 instead of the subtotal. Hence percentage is 100%

Any idea what could be causing it?

[raji_n]

@logistics_power  if my solution is helpful, please mark it as answered, so that other users can find it easily.