The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Dear All,
I have two (stapled) line charts, which I want to integrate in one chart.
* Chart 1: shows value development over time for three customer types, where classification 'False'
* Chart 2: And I have a chart showing value development over time for a classification 'True' / 'False'.
Here I would like to combine both charts in one chart (chart 2 on top of chart 1).
I tried to combine the fields 'Customer type' and 'Classified' as a legenda, but it seems this cannot be combined. Do one of you have a solution to fix?
Thanks!
Solved! Go to Solution.
Hi, @Anonymous
According to your description, you want the total just show the [Classfied]= True?
If this , you can add the filter logic in dax:
Measure = var _cur_legand = SELECTEDVALUE('Legand'[Customer type])
var _cur_month = MAX('Table'[Month])
var _t = SUMMARIZE(ALLSELECTED('Table') , 'Table'[Classified],'Table'[Customer type] , 'Table'[Month] , "Value",[Value Measure])
var _total =SUMX( FILTER(_t , [Month] = _cur_month && [Classified]=TRUE()),[Value])
var _type =SUMX( FILTER(_t , [Customer type] = _cur_legand && [Month] = _cur_month) , [Value])
return
IF(_cur_legand="TOTAL", _total ,_type)
The result is as follows:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi , @Anonymous
According to your description, you want to combine two line chart, like this:
Here are the steps you can refer to :
(1)We can click "New Table" and enter this:
Legand = UNION( VALUES('Table'[Customer type]) ,{"TOTAL"})
(2)Then we can create a measure :
Measure = var _cur_legand = SELECTEDVALUE('Legand'[Customer type])
return
IF(_cur_legand="TOTAL", SUM('Table'[Value]) , CALCULATE( SUM('Table'[Value]), TREATAS({_cur_legand},'Table'[Customer type])))
(3)Then we put the measure and the field we need on the visual and we get it:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks a lot for your suggestion and it helps me a few steps ahead again.
I simplified the data set in my request to one table with a few fields. In reality the field 'Value' is a measure . And a measure is behaving differently in the DAX formula.
In below part of the formula I get an error, saying it cannot find the table in which the measure is defined. (in beneath example table name is 'Table' and the measure is 'Value'
IF(_cur_legand="TOTAL", SUM('Table'[Value]) , CALCULATE( SUM('Table'[Value])
Any suggestio how to solve?
Thanks!
Hi, @Anonymous
Do you mean the [value] in your side is a measure?If the measure is complex, i think we need to use "var" define the paramter and then judge the situation according to your need.
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks for your reply.
Yes, the [value] in the formula is a measure. The measure itself is a simple measure, since it's a sum of a value in a table. How could a "var" formula look like in this case?
Thanks!
Hi , @Anonymous
If the value is a measure , i have two suggestion for you :
(1)We can make this value as a calculated column in table like a static value.
(2)We can use the summarize() dunction and addcolumns() function to create a virtual table , and then we calculate the value we want in logic.
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks.
How would the VAR function of the first option look like?
Hi , @Anonymous
In the above sample .pbix file , i create a measure to test:
Value Measure = SUM('Table'[Value])
Then we can create a measure like this:
Measure = var _cur_legand = SELECTEDVALUE('Legand'[Customer type])
var _cur_month = MAX('Table'[Month])
var _t = SUMMARIZE(ALLSELECTED('Table') , 'Table'[Classified],'Table'[Customer type] , 'Table'[Month] , "Value",[Value Measure])
var _total =SUMX( FILTER(_t , [Month] = _cur_month),[Value])
var _type =SUMX( FILTER(_t , [Customer type] = _cur_legand && [Month] = _cur_month) , [Value])
return
IF(_cur_legand="TOTAL", _total ,_type)
Then we can also get the visual like this:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks, the good news is that I am able to reproduce the new measures. However I am still struggling with the field 'Classified'. The total line should only show the records of the field 'Classified' with a value 'True' (currently it shows both True and False). When I use the filter for this, also customer types A, B and C will show values 'True' (which is wrong, since for these customer types A, B, and C they should show both True and False).
Here the customer type 'Total' should not be a sum of values of customer types A, B and C, but just showing the total value per month of Classified as True.
Thanks in advance for any other suggestion.
Hi, @Anonymous
According to your description, you want the total just show the [Classfied]= True?
If this , you can add the filter logic in dax:
Measure = var _cur_legand = SELECTEDVALUE('Legand'[Customer type])
var _cur_month = MAX('Table'[Month])
var _t = SUMMARIZE(ALLSELECTED('Table') , 'Table'[Classified],'Table'[Customer type] , 'Table'[Month] , "Value",[Value Measure])
var _total =SUMX( FILTER(_t , [Month] = _cur_month && [Classified]=TRUE()),[Value])
var _type =SUMX( FILTER(_t , [Customer type] = _cur_legand && [Month] = _cur_month) , [Value])
return
IF(_cur_legand="TOTAL", _total ,_type)
The result is as follows:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |