cancel
Showing results for
Did you mean:
Helper I

## Combine two graphs with different values but similar content

How can I combine 2 graphs into one when the values have different meaning (one is # App Views, another one is # Purchases) but on a surface have  similar values: '0, 1, 2, 3, 4, 5, etc."

At the momnent I have these 2 graphs:

Here you can find the actual .pbix file: https://www.dropbox.com/s/yhua9xszvcmj2df/My%20test1.pbix?dl=0

I need to combine them into one:

1 ACCEPTED SOLUTION
Community Support

Hi @juliausha ,

My idea is to create two separate calculation tables, convert measure into columns in the tables, and finally present the results in a summary table:

Sheet2:

``````custom table:
sheet2 = VALUES(Sheet1)
measure:
sheet2AppVisits = CALCULATE(
DISTINCTCOUNT(Sheet2[User]),
FILTER(
ALLSELECTED(Sheet2),
ISONORAFTER(Sheet2[App Visits], MIN(Sheet2[App Visits]), ASC)
)
)
new colmn:
Column = sheet2[sheet2AppVisits]``````

Sheet3:

``````sheet3 = VALUES(Sheet1)
measure:
sheet3purchases =
CALCULATE(
DISTINCTCOUNT(Sheet3[User]),
FILTER(
ALLSELECTED(Sheet3),
ISONORAFTER(Sheet3[Purchases], MIN(Sheet3[Purchases]), ASC)
)
)
column:
Column = sheet3[sheet3purchases]``````

Final table:

``````sheetrelation = DISTINCT(UNION(DISTINCT(Sheet1[App Visits]),DISTINCT(Sheet1[Purchases])))

ColumnA = IF(LOOKUPVALUE(sheet2[Column],sheet2[App Visits],sheetrelation[App Visits])=BLANK(),0,LOOKUPVALUE(sheet2[Column],sheet2[App Visits],sheetrelation[App Visits]))

ColumnB = IF(LOOKUPVALUE(sheet3[Column],sheet3[Purchases],sheetrelation[App Visits])=BLANK(),0,LOOKUPVALUE(sheet3[Column],sheet3[Purchases],sheetrelation[App Visits]))``````

Output:

Best Regards

Lucien

4 REPLIES 4
Community Support

Hi @juliausha ,

My idea is to create two separate calculation tables, convert measure into columns in the tables, and finally present the results in a summary table:

Sheet2:

``````custom table:
sheet2 = VALUES(Sheet1)
measure:
sheet2AppVisits = CALCULATE(
DISTINCTCOUNT(Sheet2[User]),
FILTER(
ALLSELECTED(Sheet2),
ISONORAFTER(Sheet2[App Visits], MIN(Sheet2[App Visits]), ASC)
)
)
new colmn:
Column = sheet2[sheet2AppVisits]``````

Sheet3:

``````sheet3 = VALUES(Sheet1)
measure:
sheet3purchases =
CALCULATE(
DISTINCTCOUNT(Sheet3[User]),
FILTER(
ALLSELECTED(Sheet3),
ISONORAFTER(Sheet3[Purchases], MIN(Sheet3[Purchases]), ASC)
)
)
column:
Column = sheet3[sheet3purchases]``````

Final table:

``````sheetrelation = DISTINCT(UNION(DISTINCT(Sheet1[App Visits]),DISTINCT(Sheet1[Purchases])))

ColumnA = IF(LOOKUPVALUE(sheet2[Column],sheet2[App Visits],sheetrelation[App Visits])=BLANK(),0,LOOKUPVALUE(sheet2[Column],sheet2[App Visits],sheetrelation[App Visits]))

ColumnB = IF(LOOKUPVALUE(sheet3[Column],sheet3[Purchases],sheetrelation[App Visits])=BLANK(),0,LOOKUPVALUE(sheet3[Column],sheet3[Purchases],sheetrelation[App Visits]))``````

Output:

Best Regards

Lucien

Helper I

Hi @v-luwang-msft thank you very much for your solution! It's impressive how you managed to solve it. It works like charm 🙂

Super User

@juliausha , Assume these are column values.

You can generate a series as table

Seq = generateseries(1,200,1)

Join both these with value column of this table and use value in x-axis of visual

If both columns are from one table one join will be inactive. activate that using userelationship in a measure

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Helper I

It looks like that I did something wrong because one of my joins dosn't work properly:

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.