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'm coming with you about this topic. 
I try to add and modify your code 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]

Hello  , 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 !

[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  for your helping !