Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RachnaV
Helper I
Helper I

Grouping in a measure

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 ResultCurrent Result and Desired Result

4 REPLIES 4
RachnaV
Helper I
Helper I

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. ReportMockup.png

v-zhenbw-msft
Community Support
Community Support

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"))

 

Group 1.jpg

 

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"))

 

Group 2.jpg

 

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

 

Group 3.jpg

 

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,

 

Group 4.jpg

 

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
Super User
Super User

@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
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.