Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NavaneethaRaju
Helper IV
Helper IV

Need Help with Measure

Hi, 

I created a measure for Total sum value respective of each tag id within selected period. There i have column that compared Tag owner name and order owner name. Its resulting a string Used or Shared like this.

Total Procedures(Other Territory) =
SUMX (
VALUES ( 'Tag Usage Fact'[Tag ID] ),
CALCULATE (
SUM ( 'Procedure Fact'[Procedures] ),
--ALLEXCEPT ( 'Account Dim', 'Account Dim'[Account ID] ),
ALLEXCEPT (
'Tag Usage Fact',
'Tag Usage Fact'[Tag ID]
),
FILTERS ( 'Tag Usage Fact'[Order Date] )
)
)
Normally data is coming like this without any filters.

Tag IDSet IDSet NameSystem NameTag Owner Order Owner No of ProceduresTotal Procedures% of UsageUsage Type
297965.905CoalanColanCCGN6875%Shared
297965.905ColanCOlanCCSL2825%Shared
300984.903COALCOALCCGN1425%Shared
300984.903COALCOALGNGN3475%Used
907932.903MARSMARSCSCS3650%Used
907932.903MARSMARSCSIH3650%Shared

 

While i'm apply usage type filter the total procedure value not remain the same like previously, its also changed.

Tag IDSet IDSet NameSystem NameTag Owner Order Owner No of ProceduresTotal Procedures% of UsageUsage Type
300984.903COAlCOAlGNGN33100%Used
907932.903MARSMARSCSCS33100%Used


Advice about the measure should not change the total procedures value or advice us to do is there any modification on the existing measure or create new one.



1 ACCEPTED SOLUTION
memepu
Resolver II
Resolver II

Hi @NavaneethaRaju ,

I have only created a simple sample, please refer to it to see if it helps you.

Need Help with Measure.pbix

Create a measure to calculate total.

Measure = SUMX(ALL('Table'),'Table'[VALUE])

And the measure will not be changed by slicer.

Then you can create measures based on the measure.

memepu_0-1660195006430.png

memepu_1-1660195026343.png

If I have still misunderstood your meaning, you can create simple data with excel, then show me the screenshots about the data and the desired output your want.

 

best regards.

 

View solution in original post

13 REPLIES 13
memepu
Resolver II
Resolver II

Hi @NavaneethaRaju ,

I have only created a simple sample, please refer to it to see if it helps you.

Need Help with Measure.pbix

Create a measure to calculate total.

Measure = SUMX(ALL('Table'),'Table'[VALUE])

And the measure will not be changed by slicer.

Then you can create measures based on the measure.

memepu_0-1660195006430.png

memepu_1-1660195026343.png

If I have still misunderstood your meaning, you can create simple data with excel, then show me the screenshots about the data and the desired output your want.

 

best regards.

 

memepu
Resolver II
Resolver II

Hi @NavaneethaRaju ,

What is the data in Procedure Fact'[Procedures]? It seems you are using about 2 tables.

Please change the measure.

Total Procedures(Other Territory) =
SUMX (
ALL ( 'Tag Usage Fact'[Tag ID] ),
CALCULATE (
SUM ( 'Procedure Fact'[Procedures] ),
--ALLEXCEPT ( 'Account Dim', 'Account Dim'[Account ID] ),
ALLEXCEPT (
'Tag Usage Fact',
'Tag Usage Fact'[Tag ID]
),
FILTERS ( 'Tag Usage Fact'[Order Date] )
)
)

If it still does not help, please provide your pbix file without privacy information and desired output.

 

Best regards!

Hi @memepu , 
Procedures fact is the seperate table, that table only have the procedure count.
Now i can share pbix or excel file with you. Please share any email and outlook mail id.

Hi @NavaneethaRaju ,

Please share the pbix file upload by  onedrive or google.

 

Best regards.

 

Hi @NavaneethaRaju ,

I can't view your file for some specific reason, what kind of output do you want?

 

How to Get Your Question Answered Quickly 

 

Best regards.

 

Hi @memepu
I need a measure to display the total value. even if I'm selecting Usage Type slicer, the total value should not change.  but the total should be selected period.

NavaneethaRaju
Helper IV
Helper IV

Hi @amitchandak ,

can you please help me out this. The [Total Procedures(Other Territory), that i wrote should affect the total of the respective tag id. If i select any one of the usage type, it should show only the exact %. 

For example if i select Used in visual leverl filter, it should show 75% for tag ID- 300, But its showing 100%. 

I dont know where i have to change the measure or advice me.

SpartaBI
Community Champion
Community Champion

@NavaneethaRaju little hard to say cause I can't tell from the details which are columns and which are measures/ implicit measures in your table visual.
Try this for start:

 

Total Procedures(Other Territory) =
SUMX (
    VALUES ( 'Tag Usage Fact'[Tag ID] ),
    CALCULATE (
        SUM ( 'Procedure Fact'[Procedures] ),
        REMOVEFILTERS(),
        VALUES('Tag Usage Fact'[Tag ID])
    )
)

 




2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Showcase Report – Contoso By SpartaBI

Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Hi @SpartaBI ,
I have used the above measure only, there is no implicit measure. I created a measure for sum the procedures of respective tag id in selected date range. the above measure would be giving the same result.  
I'm using ALLEXCEPT, So i'm not able apply any other filters in that measure. Please advice.

@NavaneethaRaju do you want to come to zoom / teams to look together?

@SpartaBI , Yeah can we meet if possible in google meet. navaneetharaju10@gmail.com

@NavaneethaRaju ping me when you are here

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.