Grouping in a measure

[RachnaV]

Hi,

I have a measure called Measure 3 which is sum of Measure 1 and Measure 2.

Where ever the Proportion class and Treatment Unit combo have Funding Type as both W and WMH, then the Measure 3 value should be calculated as Measure 1 (getting calculated only for FundingType = WMH, it will be blank for other Funding Types) + Measure 2 (getting calculated for Funding Type <> WMH, it will be blank for Funding Type = WMH) but its value needs to be displayed only at the first row of the Treatment Unit and remaining rows of cell Measure 3 should be blank in that Treatment unit.

I am attaching the excel snapshot showing how it is getting calculated and displayed right now along with the desired result and format. How to achieve this ?

ThanksCurrent Result and Desired Result

[RachnaV]

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. 

[v-zhenbw-msft]

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.

[amitchandak]

@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.

[parry2k]

@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...