The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi
Can you please help me to solve this issue.
I try to calculate the distincount number of "reference secondaire " which ' somme de mt facturé = 0"
I want to find the total result for each month
in this board but my measure is not correct
Solved! Go to Solution.
@yamina in my opinon your previous result wasn't correct, because the sum of 1270 + 1065 +1028 + 853 + 794 + 905 + 635 is 6550, not 6365. If you want to share the pbix file I can help you.
Alternative approach using calculated columns and measures:
1. Calculated Column for Sum Check:
Is_Sum_Zero =
IF( CALCULATE( SUM(HG[montant_facture]), ALLEXCEPT(HG, HG[reference_secondaire]) ) = 0, 1, 0 )
2. Measure for Distinct Count Using Calculated Column:
Distinct_Count_Reference_Zero_Sum_Alt =
CALCULATE( DISTINCTCOUNT(HG[reference_secondaire]), FILTER(HG, HG[Is_Sum_Zero] = 1) )
Please if it's correct, then accept my answer as solution.
BBF
Hi
Thank you very clear , it seems to do the job but I have a little difference int he result that I have to understand
@yamina in my opinon your previous result wasn't correct, because the sum of 1270 + 1065 +1028 + 853 + 794 + 905 + 635 is 6550, not 6365. If you want to share the pbix file I can help you.
Alternative approach using calculated columns and measures:
1. Calculated Column for Sum Check:
Is_Sum_Zero =
IF( CALCULATE( SUM(HG[montant_facture]), ALLEXCEPT(HG, HG[reference_secondaire]) ) = 0, 1, 0 )
2. Measure for Distinct Count Using Calculated Column:
Distinct_Count_Reference_Zero_Sum_Alt =
CALCULATE( DISTINCTCOUNT(HG[reference_secondaire]), FILTER(HG, HG[Is_Sum_Zero] = 1) )
Please if it's correct, then accept my answer as solution.
BBF
Unfortunately i can 't share you the file it is not supported
However I try with your second alternative measures I get the same result so I suppose that it is correct. How can i share you the file ?
@yamina you can upload it in Drive and then share here the link.
But if also the second result is the same, that's the correct solution. Can you make a manual check for example on one month?
BBF
@yamina Hi!
// Step 1: Calculate the sum of "montant_facture" for each "reference secondaire"
Sum_Mt_Factures_Per_Reference =
CALCULATE(
SUM(HG[montant_facture]),
ALLEXCEPT(HG, HG[reference_secondaire])
)
// Step 2: Create a new column to identify if the sum is zero
Is_Sum_Zero =
IF(
[Sum_Mt_Factures_Per_Reference] = 0,
1,
0
)
// Step 3: Calculate the distinct count of "reference secondaire" where sum is zero
Distinct_Count_Reference_Zero_Sum =
CALCULATE(
DISTINCTCOUNT(HG[reference_secondaire]),
FILTER(HG, [Sum_Mt_Factures_Per_Reference] = 0)
)
BBF
User | Count |
---|---|
28 | |
10 | |
8 | |
6 | |
5 |
User | Count |
---|---|
33 | |
13 | |
12 | |
9 | |
7 |