cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

In Powerbi how to get SUM of values present in measure

I have below data in table

 Id Dispatch ID In store Send 1 4 1 1 1 4 -1 1 2 7 -1 0 2 7 1 1 3 8 1 1 3 8 1 1 3 9 1 1

Now i need distinct count of (id,dispatch id) which is send

In this case it should only choose where In store=1 and send =1
As Id 1 has Instore =-1 and shared also =1 Then this should be ignored

Total in card it should be Count of (2,(3,8),(3,9))=3

ID 1 is ignored as it has combination that it was not present in stored but then also send.

I tried couple of measure but all failed at certain point with different dataset.

1 ACCEPTED SOLUTION
Super User

Hi,

Please check the below picture and the attached pbix file.

``````Expected result measure: =
VAR _t =
SUMMARIZE ( Data, Data[Id], Data[Dispatch ID] ),
"@count",
COUNTROWS (
CALCULATETABLE ( FILTER ( Data, Data[In store] * Data[Send] = 1 ) )
),
"@countnotwant",
COUNTROWS (
CALCULATETABLE ( FILTER ( Data, Data[In store] * Data[Send] = -1 ) )
)
)
RETURN
COUNTROWS ( FILTER ( _t, [@countnotwant] = BLANK () ) )
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

Hi,

Please check the below picture and the attached pbix file.

``````Expected result measure: =
VAR _t =
SUMMARIZE ( Data, Data[Id], Data[Dispatch ID] ),
"@count",
COUNTROWS (
CALCULATETABLE ( FILTER ( Data, Data[In store] * Data[Send] = 1 ) )
),
"@countnotwant",
COUNTROWS (
CALCULATETABLE ( FILTER ( Data, Data[In store] * Data[Send] = -1 ) )
)
)
RETURN
COUNTROWS ( FILTER ( _t, [@countnotwant] = BLANK () ) )
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors