Forum Discussion
Sum Distinct Values (Two variables: PI and IO with multiple Partners)
Hello Community :smileyhappy:
Same Objective in all below scenarios (2 scenarios, but one Pivot. Is it possible using DAX?):
SUM UP Distinct Values (both for PI and IO, but special for IO I need to have a down level when it is possible to break total IO cost BY Partner) and then show this scenario in a Pivot)
Comments:
1- Despite I am a very beginner DAX user, I really tried for three weeks long to think about an one-way solution for this kind of report, but I have failed in all.
2- I even tried to create some sort of relationship between tables, but again have failed (fail example below).
| Scenario 1 (Fact Table) | |||||
| PI | Budget | IO | Partner | Cost | |
| 1 | 10 | 1 | Sizmek | 1 | |
| 1 | 10 | 1 | ROI | 2 | |
| 1 | 10 | 1 | Tail | 1 | |
| 1 | 10 | 1 | MediaMath | 1 | |
| Pivot Objective: | |||||
| PI | Budget | IO | Partner | Cost | Markup (I know how to set a measure) |
| 1 | 10 | 1 | Sizmek | 1 | |
| ROI | 2 | ||||
| Tail | 1 | ||||
| MediaMath | 1 | ||||
| Total | 10 | 5 | 5 | ||
| Pivot Failure Example | |||||
| PI | Budget | IO | Partner | Cost | Markup |
| 1 | 10 | 1 | Sizmek | 1 | 9 |
| 1 | 10 | 1 | ROI | 1 | 9 |
| 1 | 10 | 1 | Tail | 1 | 9 |
| 1 | 10 | 1 | MediaMath | 1 | 9 |
| Can´t summarize none |
| Scenario 2 (Fact Table) | |||||
| PI | Budget | IO | Partner | Cost | |
| 1 | 20 | 1 | Sizmek | 1 | |
| 1 | 20 | 1 | ROI | 2 | |
| 1 | 20 | 1 | Tail | 1 | |
| 1 | 20 | 1 | MediaMath | 1 | |
| 1 | 20 | 2 | Sizmek | 1 | |
| 1 | 20 | 2 | ROI | 2 | |
| 1 | 20 | 2 | Tail | 1 | |
| 1 | 20 | 2 | MediaMath | 1 | |
| Pivot Objective: | |||||
| PI | Budget | IO | Partner | Cost | Markup |
| 1 | 20 | 1 | Sizmek | 1 | |
| 1 | ROI | 2 | |||
| 1 | Tail | 1 | |||
| 1 | MediaMath | 1 | |||
| 2 | Sizmek | 1 | |||
| 2 | ROI | 2 | |||
| 2 | Tail | 1 | |||
| 2 | MediaMath | 1 | |||
| Total | 20 | 10 | 10 |
Thanks a lot,
Jimmy
3 Replies
bajimmy1983 You will be able to achieve somewhat similar to what you want in easy way by using Matrix visual and using PI, Budget and IO fields for Rows, Partner for Columns and Cost as Values.
- Hi ankitpatira,
Thanks for your contribution.
I think I made a mistake and forgot to note that I have Billings and Costs amounts different fact tables.
On both tables I have some common fields like PI number, client, IO numbers, etc. I think my challenge is on how to connect both tables, create some measures and then set the power pivot (I also avoiding myself to use calculated columns).
Regards,Hi bajimmy1983,
Using Matrix should work here.
For connecting tables, either we could combine those tables under Query Editor, or create relationships between them.
You may first take a look at the article below, about query tasks, or creating relationship:
Common query tasks in Power BI Desktop
Create and manage relationships in Power BI Desktop
If any further help needed, please post back.
Regards
