Sum Distinct Values (Two variables: PI and IO with multiple Partners)

[bajimmy1983]

Hello Community 

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

[ankitpatira]

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

[bajimmy1983]

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,

[v-micsh-msft]

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