Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team,
DAX - based on UID (Unique) total sum value subtraction with each UID Category like row wise, could you please help me.
Note:
1) stock and sal_total both are measures value (calculated new measures)
2) Blac_Manual -> expected result (Manual Calculation
3) Cat -> row wise subtract UID sum of total like first ex : GH-258 Stock is 14 - 5 (UID sum) = expecting result is 9
formul Not working :
Thanks,
KV's
Solved! Go to Solution.
Hi @Anonymous ,
According to your description, I create this data:
Here are the steps you can follow:
1. Create measure.
Stock: Cat category value when uid is consistent
stock =
CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[Cat]=MAX('Table'[Cat])))
sal_total:Sum by [cat] class UID
sal_total =
var _UID=CALCULATE(MAX('Table'[UID]),FILTER(ALL('Table'),[Cat]=MAX('Table'[Cat])))
return
CALCULATE(COUNT('Table'[UID]),FILTER(ALL('Table'),'Table'[UID]=_UID))
Blac_Manual:Expected results
Balc_Manual = [stock]-[sal_total]
2. Result
You can downloaded PBIX file from here.
If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
According to your description, I create this data:
Here are the steps you can follow:
1. Create measure.
Stock: Cat category value when uid is consistent
stock =
CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[Cat]=MAX('Table'[Cat])))
sal_total:Sum by [cat] class UID
sal_total =
var _UID=CALCULATE(MAX('Table'[UID]),FILTER(ALL('Table'),[Cat]=MAX('Table'[Cat])))
return
CALCULATE(COUNT('Table'[UID]),FILTER(ALL('Table'),'Table'[UID]=_UID))
Blac_Manual:Expected results
Balc_Manual = [stock]-[sal_total]
2. Result
You can downloaded PBIX file from here.
If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Logic is not very clear.
Create a new measure like
balc = sumx(filter(allselected(Sheet2), [UID] = max([UID]) ),[Stock]) - maxx(filter(allselected(Sheet2), [UID] = max([UID]) ),[Production])
Hi @amitchandak ,
It's working DIM based total sum, but I'm expecting DIM while including Category then total volume split like below,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |