Grouping in a measure

RachnaV it is hard to understand from the image how raw data looks like. it is better if you put a sample data in table format and expected output.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 

RachnaV ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
But refer if this can help

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
Appreciate your Kudos.

Hi RachnaV ,

We can use the following steps to meet your requirement.

1. Create a measure that Funding Type = “W”. and create a measure that Funding Type = “WMH”.

W value = CALCULATE(SUM('Table'[values]),'Table'[Type]="W")

WMH = CALCULATE(SUM('Table'[values]),FILTER('Table','Table'[Type]="WMH"))

2. Then we can create a calculate table to get the sum of “WMH”.

Table 2 = SUMMARIZE('Table','Table'[name],"sum",CALCULATE(SUM('Table'[values]),'Table'[Type] = "WMH"))

3. We need to create a relationship between Table and Table 2 based on name. The direction is both.

4. At last we can create the total measure like this,

Total = 
var WMH__ = CALCULATE(SUM('Table 2'[sum]))
var x = [W value] + WMH__
return
IF(ISBLANK([W value]),BLANK(),x)

The result like this,

If it doesn’t meet your requirement, could you please provide a mockup sample  based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi All,

I have replicated the scenario using Contoso model and uploading the snapshot of mock-up report how it is required with assumptions, including the two measures that need to be created and the formula for each measure.
Hope this helps in clarifying what type of measure is required and how it should be calculated. It would be helpful for other readers as well if they face a similar scenario.

Financial Year Short Name, Financial Month Short Name, CustomerType and Have Kids at Home will be the filters applied to the report.

Although the Formula is mentioned in mock-up snapshot, mentioning it again for easy reference :

Total Number of Units Sold is a measure that would be used in calculating below two measures. It is calculated as DISTINCTCOUNT of number of units sold from Sales Table.

Formula for Sales Weightage Ratio - Customer Occupation will be :
1. For Category, Customer Occupation combination that does not have marital status "M" -> Total Number of Units Sold for each category(like 1600 for Cell Phones) * Unit Sold Weightage corresponding to that category, customer occupation and marital status.
2. Category and Customer Occupation combination that has both marital statuses - "M" and "S" :
a. Wherever Occupation has marital status as only "S" -
Total Number of Units Sold in each category(like 1600 for Cell Phones) * Unit Sold Weightage (SAME CALCULATION AS IN POINT 1 above) corresponding to that category, customer occupation and marital status.
b. Wherever Occupation has both marital statuses- "S"and "M" - Total Number of Units Sold for each category(like 1600 for Cell Phones) * Unit Sold Weightage for both S and M marital status (like 225 for Cell Phones -> Professional occupation) i.e. weightage value for corresponding category and occupation.

Formula for Units Sold Weightage will be :
1. For Category and Customer Occupation combination that does not have marital status "M" -> Total Number of Units Sold in each category,customer occupation and marital status (like 100 for Cell Phones, Clerical and Single customers) * Unit Sold Weightage corresponding to that category, customer occupation and marital status.

2. Category and Customer Occupation combination that has both marital statuses - "M" and "S" :
a. Wherever Occupation has marital status only "S" -
Total Number of Units Sold in each category,customer occupation and marital status (like 100 for Cell Phones, Clerical and Single customers) * Unit Sold Weightage (SAME CALCULATION AS IN POINT 1 above) corresponding to that category, customer occupation and marital status.
b. Wherever Occupation has both marital statuses "S" and "M" - Total Number of Units Sold in corresponding category and occupation (like 175.5 for Cell Phones, Management combination) * Unit Sold Weightage for that category and occupation (like 0.3 for Cell Phones and Management ).

I am seeking help in creating Measures - Sales Weightage Ratio - Customer Occupation AND Units Sold Weightage so that it meets above stated criteria while calculation.