Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Need to sum up values of the largest amount based on badge number and based on selected year. Below is the sample table for easy reference. I appreciate your help
Bold data (the largest of the amount for each badge number) to be added.
BadgeNo | Amount | Year |
1 | 2000 | 2018 |
1 | 3000 | 2018 |
1 | 1000 | 2019 |
1 | 5000 | 2018 |
2 | 6000 | 2018 |
2 | 8000 | 2018 |
2 | 2000 | 2019 |
2 | 5000 | 2018 |
3 | 2000 | 2018 |
4 | 5000 | 2018 |
4 | 6000 | 2018 |
5 | 5000 | 2018 |
Total for Year 2018 (based on the largest amount per badge number | 26000 |
@CJ_96601 Please create a new table as below and use the Year and Amount field from this new table in your visuals.
Test219Out = SUMMARIZE(Test219MaxSum,Test219MaxSum[Year],Test219MaxSum[BadgeNo],"Amount",MAX(Test219MaxSum[Amount]))
Proud to be a PBI Community Champion
Thank you for your reply. Can you share the pbix sample file.
error. Multiple columns cannot be converted to a scalar value
@CJ_96601 Hope you using above DAX to create as a new table.
Proud to be a PBI Community Champion
I use my table and repace the syntax with the proper column, field.
@CJ_96601 Ok, just for your reference
Test219Out --> NewTable Test219MaxSum --> Your Source Table or Existing Table
Proud to be a PBI Community Champion
If i understand you correctly, you want me to have a new table?
The data i have shared is just part of the so many records (15000 rows) and for that cannot create a new table.
Need to use existing table and show the visuals based on my requirements
@CJ_96601 Ok, if you don't want to create an another table (summarized). Then follow the below steps as an alternate method.
Please add a new column to your source table as below
MaxFlag = VAR _MaxVal = CALCULATE(MAX(Test219MaxSum[Amount]),FILTER(Test219MaxSum,Test219MaxSum[BadgeNo]=EARLIER(Test219MaxSum[BadgeNo]) && Test219MaxSum[Year] = EARLIER(Test219MaxSum[Year]))) RETURN IF(Test219MaxSum[Amount] = _MaxVal,"Y","N")
Then have a visual level filter as below.
Hope this helps !!
Proud to be a PBI Community Champion
Hi,
I used this and it works..
@CJ_96601 Please update your Measure logic as below and use your Year field as a filter.
Test219 = VAR _Total = SUMX(SUMMARIZE(Test219MaxSum,Test219MaxSum[BadgeNo],Test219MaxSum[Year],"Amount",MAX(Test219MaxSum[Amount])),[Amount]) RETURN IF(HASONEFILTER(Test219MaxSum[Year]),SUMX(DISTINCT(Test219MaxSum[BadgeNo]),CALCULATE(MAXX(Test219MaxSum,Test219MaxSum[Amount]))),_Total)
Proud to be a PBI Community Champion
Furthermore, if i make a relationship (Year), if i click year 2020, the result is zero , null, as there is no record in 2020. What i would like to have is to have 2019 sum even if i click 2020.
Regards,
Hi, thank you for your response.
The date (year) i am currently using is in another table.
Do i need to make a relationship between the two tables.
As of now, there is only one active relationship in power bi
@CJ_96601 Yes it should have a relationship based on the Year field from which table you want to get it as a filter.
Proud to be a PBI Community Champion
is it possible not having a relationships but create the filter by year (in the measure)?
Thanks.
Shall i add column in Power bi query or in excel file?
@CJ_96601 No, it is in Power Query Editor, once you click on the "Edit Queries"
Proud to be a PBI Community Champion
User | Count |
---|---|
122 | |
109 | |
97 | |
60 | |
57 |
User | Count |
---|---|
137 | |
114 | |
102 | |
70 | |
56 |