Filtrer in one line

[KevinSV]

Hello everyone, 

I need your help on my Power BI Desktop. 
First of all, i import my data from a SQL Server for my report. I put some slicers for filter and give a table after the filtrer. 
In this slicer, there is a column which called TCOMPTE which had the account number. I need all the number for having the perfect balance ammount of debits and credits.

In this image as you can see i have a lot of Tcompte numbers which are lines by lines. I want to fix the into an unique line where the Tcompte number will be 48700000 with adding the good amount into MDEBITS and DCREDITS (sum) WITH ONE CONDITION, I exclude all the Tcompte number begining with '7' because i want them lines by lines (above my screen there are other lines with '7' beginning number account). 
In my table (visualisation) i have few columns : CJOURNAL,TCOMPTE,CPIECE,TREF,CAUX,CSOC,MDEBIT,MCREDIT,DECRIT,TLIB,CANALYT1,CANALYT2,CANALYT3,CANALYT4,CANALYT5,MDEBITS_Coeff,DCREDITS_Coeff,CREFGESTION,CLETTRE,DLETTRE,DPAIEMENT,CMODREGL. 
All of the columns will be blank except : CJOURNAL which is unique for all lines (so if I put 'A' for Cjounral, all the lines of the table will have 'A' in the column) CSOC which is unique for all the lines (the number of the society) DECRIT which is unique too, CANALYT3 which is unique with the other line too. 
When i said unique that is because all the lines of the table have the same value selected (same value, date...).
In red, this 2 columns are measure :  

DCREDITS_Coeff =

VAR coeffselect =

    SELECTEDVALUE ( Coefficient[Pourcentage]; 1 )

RETURN

    IF(ISINSCOPE(PCA[ANALYT]);

    MAX(PCA[CREDITS]) * coeffselect;

    SUMX(

        PCA;

        PCA[CREDITS] * coeffselect

    )

    ).
I'm sorry if it is too long but my problem looks like very complexe so i try to had more details. 
Do you have any issues ? 
thanks a lot !

[KevinSV]

Hello Everyone, 
So i continue my code step by step and i see few problems. 
First of all is the adding of the CJOURNAL, DECRIT still not working with few corrections. 
Second one, I have the wrong amount into the 48700000 line : 

As you can see I have more than 19k on DCREDITS for the Tcompte which are not begining with 7 but i need for the FAC CJOURNAL an amount of 137,23 : 

So a total amount of 151 255,07. The 487 line is taking all the amount with not filtering the Cjournal (I have 3 different CJOURNAL for the '424' CSOC) because it doesn't have one on its line and so it gives me 19388,55 instead of 137,23. 
Here is my template : 

In the slicer Cjournal, i have normaly 2 other with FAC but the slicer shows me only the '7' Tcompte beginning Cjournal (the other have an another Tcompte number) : 

As you can see all the Cjournal have a tcompte number not begining with 7. 
Do you have any idea to add the Cjournal and DECRIT on my first line ? 
The selectvalue and All didn't work when i tried it. 
If you need more details, tell me i will do my best ! 
PS : Thanks again for your helping. I have progressed a lot on my project ! 

[KevinSV]

Hello everyone, so i'm coming with you about this topic. 
I try to add and modify your code @rajendraongole1 and i am on a good way about what i want (Thanks again) : 

CombinedTable2 =

    VAR CJOURNAL_UNIQUE = SELECTEDVALUE('PCA'[CJOURNAL])

    VAR DECRIT_UNIQUE = SELECTEDVALUE('PCA'[DTECRIT])

    VAR CANALYT3_UNIQUE = SELECTEDVALUE('PCA'[ANALYT])

    RETURN

UNION(

SELECTCOLUMNS(

    SUMMARIZE(

        FILTER('PCA'; 'PCA'[IsAggregateRow] = 1);

        'PCA'[SOCIETE];

        "TCOMPTE"; "48700000";

        "CJOURNAL"; CJOURNAL_UNIQUE;

        "MDEBITS"; [Total_MDEBITS];

        "DCREDITS"; [Total_DCREDITS];

        "DECRIT"; DECRIT_UNIQUE;

        "CANALYT3"; CANALYT3_UNIQUE;

        "TLIB"; SELECTEDVALUE(PCA[LIB2])

    );

"CJOURNAL"; SELECTEDVALUE(PCA[CJOURNAL]);

"TCOMPTE"; "48700000";

"CSOC";'PCA'[SOCIETE];

"MDEBITS"; [Total_MDEBITS];

"DCREDITS"; [Total_DCREDITS];

"DECRIT"; SELECTEDVALUE('PCA'[DTECRIT]);

"CANALYT3"; SELECTEDVALUE('PCA'[ANALYT]);

"TLIB"; SELECTEDVALUE(PCA[LIB2])

);

SELECTCOLUMNS(

    FILTER('PCA'; LEFT('PCA'[TCOMPTE]; 1) = "7");

    "CJOURNAL"; 'PCA'[CJOURNAL];

    "TCOMPTE"; 'PCA'[TCOMPTE];

    "CSOC"; 'PCA'[SOCIETE];

    "MDEBITS"; 'PCA'[DEBITS];

    "DCREDITS"; 'PCA'[CREDITS];

    "DECRIT"; 'PCA'[DTECRIT];

    "CANALYT3"; 'PCA'[ANALYT];

    "TLIB"; 'PCA'[LIB2]

))

The problem is : I want to add the Cjournal and DECRIT values on the line (there are the same for all the lines) : 

That is the result i got when i only filtrer by the CSOC but when i add another filtrer on the DECRIT for example, the first line will disapear because he is blank. And my code didn't work on adding the value with the selectedvalue. 
Do you have any ideas ? 
Thanks you for helping ! 

[rajendraongole1]

Hi @KevinSV - To ensure that values for CJOURNAL and DECRIT remain visible across all rows, you can try adjusting your DAX code by using ALL or ALLEXCEPT to ignore specific filters, so these columns retain their values irrespective of filtering on other columns.

CombinedTable2 =
VAR CJOURNAL_UNIQUE = SELECTEDVALUE('PCA'[CJOURNAL])
VAR DECRIT_UNIQUE = SELECTEDVALUE('PCA'[DTECRIT])
VAR CANALYT3_UNIQUE = SELECTEDVALUE('PCA'[ANALYT])

RETURN
UNION(
SELECTCOLUMNS(
SUMMARIZE(
FILTER(ALL('PCA'), 'PCA'[IsAggregateRow] = 1),
'PCA'[SOCIETE],
"TCOMPTE", "48700000",
"CJOURNAL", CJOURNAL_UNIQUE,
"MDEBITS", [Total_MDEBITS],
"DCREDITS", [Total_DCREDITS],
"DECRIT", DECRIT_UNIQUE,
"CANALYT3", CANALYT3_UNIQUE,
"TLIB", SELECTEDVALUE(PCA[LIB2])
),
"CJOURNAL", CJOURNAL_UNIQUE,
"TCOMPTE", "48700000",
"CSOC", 'PCA'[SOCIETE],
"MDEBITS", [Total_MDEBITS],
"DCREDITS", [Total_DCREDITS],
"DECRIT", DECRIT_UNIQUE,
"CANALYT3", CANALYT3_UNIQUE,
"TLIB", SELECTEDVALUE(PCA[LIB2])
),
SELECTCOLUMNS(
FILTER(ALL('PCA'), LEFT('PCA'[TCOMPTE], 1) = "7"),
"CJOURNAL", CJOURNAL_UNIQUE,
"TCOMPTE", 'PCA'[TCOMPTE],
"CSOC", 'PCA'[SOCIETE],
"MDEBITS", 'PCA'[DEBITS],
"DCREDITS", 'PCA'[CREDITS],
"DECRIT", DECRIT_UNIQUE,
"CANALYT3", CANALYT3_UNIQUE,
"TLIB", 'PCA'[LIB2]
)
)

Please check the above modified one ,Hope this approach should help keep the values consistent in your table

[KevinSV]

Thanks for your answer  @rajendraongole1.
Unfortunately, it didn't work and nothing happened : 

and when i filter with the FAC Cjournal or/and a date i will have all the lines except the first one because he has no values in those columns : 

[KevinSV]

Hello @rajendraongole1 , thanks for your helping. 
All the measure works as well but when i create my table and try to have a visualisation, i don't have the same amount in the debits and credits when i filter. And that is because it looks like i have juste the TCOMPTE begining with the '7' but delete all the TCOMPTE which not begin with a '7'. But i want them but in one line only : 

on my last screen, i don't want to delete them but have all the amount in only one line where the TCompte will have the value '48700000'.
What i have now : 

as you can see the total amount are not the same. When i compare with my sql dataset,  it si normal because i don't select the other Tcompte number (where the Tcompte begins with another number than '7'). But when i select them, it rebalances the amount. 
And sorry to add another condition, but i need to put the new '48700000' TCOMPTE number which will create in the first line of the table (is that possible ?).
Thanks again for your helping !

[KevinSV]

It seems that it works when i don't add filters : 

[rajendraongole1]

Hi @KevinSV - First, create a calculated column that identifies rows where TCOMPTE does not start with "7". This will help us distinguish the rows to aggregate.

IsAggregateRow =
IF(
LEFT('YourTable'[TCOMPTE], 1) <> "7",
1,
0
)

Now, create two measures to sum MDEBITS and DCREDITS for rows where TCOMPTE doesn’t start with "7" (i.e., where IsAggregateRow = 1). This will ensure that we get a single aggregated total for these values.

Total_MDEBITS =
CALCULATE(
SUM('YourTable'[MDEBITS]),
FILTER('YourTable', 'YourTable'[IsAggregateRow] = 1)
)

Total_DCREDITS =
CALCULATE(
SUM('YourTable'[DCREDITS]),
FILTER('YourTable', 'YourTable'[IsAggregateRow] = 1)
)

To combine the aggregated row with the original rows where TCOMPTE starts with "7", you can create a new table (using DAX) with the UNION function. This table will include one aggregated row and all individual rows with TCOMPTE starting with "7".

CombinedTable =
UNION(
SELECTCOLUMNS(
FILTER('YourTable', 'YourTable'[IsAggregateRow] = 1),
"CJOURNAL", "A", -- Replace "A" with your desired unique value
"TCOMPTE", "48700000",
"CSOC", SELECTEDVALUE('YourTable'[CSOC]),
"MDEBITS", [Total_MDEBITS],
"DCREDITS", [Total_DCREDITS],
"DECRIT", SELECTEDVALUE('YourTable'[DECRIT]),
"CANALYT3", SELECTEDVALUE('YourTable'[CANALYT3])
),
SELECTCOLUMNS(
FILTER('YourTable', LEFT('YourTable'[TCOMPTE], 1) = "7"),
"CJOURNAL", 'YourTable'[CJOURNAL],
"TCOMPTE", 'YourTable'[TCOMPTE],
"CSOC", 'YourTable'[CSOC],
"MDEBITS", 'YourTable'[MDEBITS],
"DCREDITS", 'YourTable'[DCREDITS],
"DECRIT", 'YourTable'[DECRIT],
"CANALYT3", 'YourTable'[CANALYT3]
)
)

In your report, use CombinedTable in a table visual to display the combined aggregated row and individual rows for TCOMPTE values that start with "7". This table should only contain the columns CJOURNAL, TCOMPTE, CSOC, MDEBITS, DCREDITS, DECRIT, and CANALYT3 as per your requirement.

[KevinSV · Accepted solution]

Here is the update of the topic : 
So i modify my code : 

CombinedTable2 =

UNION(

SELECTCOLUMNS(

    SUMMARIZE(

        FILTER(ALL('PCA'); 'PCA'[IsAggregateRow] = 1);

        'PCA'[SOCIETE]; PCA[CJOURNAL]; PCA[DTECRIT]; PCA[ANALYT]; --here is the modify i add all the column i need on this line of the code

        "TCOMPTE"; "48700000";

        "MDEBITS"; [Total_MDEBITS];

        "DCREDITS"; [Total_DCREDITS];

        "TLIB"; SELECTEDVALUE(PCA[LIB2])

    );

"CJOURNAL"; PCA[CJOURNAL];

"TCOMPTE"; "48700000";

"CSOC";'PCA'[SOCIETE];

"MDEBITS"; [Total_MDEBITS];

"DCREDITS"; [Total_DCREDITS];

"DECRIT"; PCA[DTECRIT];

"CANALYT3"; 'PCA'[ANALYT];

"TLIB"; SELECTEDVALUE(PCA[LIB2])

);

SELECTCOLUMNS(

    FILTER(ALL('PCA'); LEFT('PCA'[TCOMPTE]; 1) = "7");

    "CJOURNAL"; 'PCA'[CJOURNAL];

    "TCOMPTE"; 'PCA'[TCOMPTE];

    "CSOC"; 'PCA'[SOCIETE];

    "MDEBITS"; 'PCA'[DEBITS];

    "DCREDITS"; 'PCA'[CREDITS];

    "DECRIT"; 'PCA'[DTECRIT];

    "CANALYT3"; 'PCA'[ANALYT];

    "TLIB"; 'PCA'[LIB2]

))

Thanks again @rajendraongole1 for your helping !