Forum Discussion
Display repeated values once
Dear All,
I have the following table [Table1]:
| Name | Date | Value1 | Value2 |
Ninja | 01/01/2021 | 300 | 250 |
| Ninja | 01/01/2021 | 200 | 250 |
Samurai | 01/01/2021 | 100 | 450 |
| Samurai | 01/01/2021 | 300 | 450 |
| Samurai | 01/01/2021 | 400 | 450 |
What I want to obtain is the following visual:
| Name | Date | Value1 | Value2 |
| Ninja | 01/01/2021 | 500 | 250 |
| Samurai | 01/01/2021 | 800 | 450 |
which is:
- for VALUE1: SUM(Value1[Table1]
- for VALUE2: just count value2 once for each name and each date. I tried with sum of value2 and dividing it by countrows but I don't get the correct TOTAL.
Any suggestion?
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
i did what you want in power query
https://1drv.ms/u/s!AiUZ0Ws7G26RhwjPCsq1Bd_XkAMP?e=eHRFWI
4 Replies
gianmarco , Create measure like
Value 1 M = SUM(Table1[Value1])
Value2 M = Sumx(Summarize(Table1,Table1[Name], Table1[Date], Table1[value 2]) ,[Value 2])
Dear amitchandak ,
Followed your suggestion but I get the sum of values2, instead of the distinct value..
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
i did what you want in power query
https://1drv.ms/u/s!AiUZ0Ws7G26RhwjPCsq1Bd_XkAMP?e=eHRFWI
