Reply
lastnn30
Post Patron
Post Patron
Partially syndicated - Outbound

Modeling is not working

Hi

I have 2 tables (FactTable and dTable1, please see below)  in an excel file. I do not want to use Power Query-->merge, to get Region from the dTable1. So created 1-to-Many relationship (PBI did it for me). I used PowerQuery to add Revenue column in FactTable ((1-discount)*unit*price).  Now when I try to insert a visual and insert  Region and Revenue, PBI did not group Regions and I can not change the function for Revenue to do SUM. What is the issue please? I PBI file is here if you are interested

 

https://drive.google.com/file/d/1HuPC_X-l5ihXFB_AkQ26DBprFCJ248IY/view?usp=sharing

 

SNAG-0466.jpg

 

SNAG-0467.jpg

1 ACCEPTED SOLUTION
avatar user
Anonymous
Not applicable

Syndicated - Outbound

Hi @lastnn30 , 

Please refer to my pbic file to see if it helps you.

Create a column firstly.

Column = CALCULATE(MAX(DTable1[Region]),FILTER((DTable1),DTable1[SalesRep]=EARLIER(FactTable[SalesRep])))

Then create a measure.

Measure 3 = CALCULATE(SUM(FactTable[Revenue]),FILTER(ALL(FactTable),FactTable[Column]=SELECTEDVALUE(FactTable[Column])))

vpollymsft_0-1646810957176.png

If I have misunderstood your meaning, please provide your desired output with more details.

 

Best Regards

Community Support Team _ Polly

 

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

View solution in original post

5 REPLIES 5
avatar user
Anonymous
Not applicable

Syndicated - Outbound

Hi @lastnn30 , 

Please refer to my pbic file to see if it helps you.

Create a column firstly.

Column = CALCULATE(MAX(DTable1[Region]),FILTER((DTable1),DTable1[SalesRep]=EARLIER(FactTable[SalesRep])))

Then create a measure.

Measure 3 = CALCULATE(SUM(FactTable[Revenue]),FILTER(ALL(FactTable),FactTable[Column]=SELECTEDVALUE(FactTable[Column])))

vpollymsft_0-1646810957176.png

If I have misunderstood your meaning, please provide your desired output with more details.

 

Best Regards

Community Support Team _ Polly

 

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

mahoneypat
Microsoft Employee
Microsoft Employee

Syndicated - Outbound

The issue is that your Revenue is set to text data type. In the query editor, change it to a numeric data type and the option to summarize it with a sum will appear. However, I encourage you to create a simple measure with the SUM function instead.

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Syndicated - Outbound

Thank you for your reply. Could you please explain how to create a measure with the SUM? Thanks again.

lastnn30
Post Patron
Post Patron

Syndicated - Outbound

Thank you very much. When I opned Power Query editor, I saw the column was formatted to 1.2 (decimal)! but despite that I clicked on the drop down menu and clicked decimal again and it is working. Thank you very much again. I wont be able to figure that out without your help.

ERD
Community Champion
Community Champion

Syndicated - Outbound

@lastnn30 , you need to change the format of the column to Decimal:

ERD_0-1646582515317.png

and then to summarize the column if you want to use it this way:

ERD_1-1646582541251.png

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

I am a Ukrainian living in Ukraine. Please, help us to survive. Here are official ways you can support us (accounts with multiple currencies):
https://bank.gov.ua/ua/about/support-the-armed-forces

USD:
BENEFICIARY: National Bank of Ukraine
BENEFICIARY BIC: NBUA UA UX
BENEFICIARY ADDRESS: 9 Instytutska St, Kyiv, 01601, Ukraine
ACCOUNT NUMBER: 400807238
BENEFICIARY BANK NAME: JP MORGAN CHASE BANK, New York
BENEFICIARY BANK BIC: CHASUS33
ABA 0210 0002 1
BENEFICIARY BANK ADDRESS: 383 Madison Avenue, New York, NY 10017, USA
PURPOSE OF PAYMENT: for crediting account 47330992708

Accounts details for other currencies (EUR|GBP|CHF|AUD|CAD|PLN) can be found here: https://bank.gov.ua/ua/about/support-the-armed-forces

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)